Saturday, February 25, 2012

Different no. of rows returned in SEM vs QA

Hi,
Any idea why when I run a SELECT stament in Query anaylser it returns 45 rows. But when I create the exact same SQL as a view in Enterprise manager it only returns 44 rows?
Thanks,
AlphCould you kindly post the query, so that we can help you better.|||Its a union query with 12 Selects. Here is the first select:

SELECT
dbo.tblWBS.PSWBS AS [PSWBS Code],
ConcatenatedWBS AS [CWBS Code],
case when CR is null then 'R' else 'C' end AS [Capital / Revenue],
NDACost AS [Cost Element],
SUM(dbo.udfBCWScost (NDACost,FiscalYear,Apr_Cost,dbo.tblWBS.PSWBS )) as Amount,
'01-04-' + ltrim(rtrim(str(FiscalYear))) AS [Start Date],
'30-04-' + ltrim(rtrim(str(FiscalYear))) AS [Finish Date],
left(dbo.tblWBS.wbs,6) + Right(dbo.tblWBS.wbs,5) AS [Charge Code]

FROM dbo.tblBCWSMonthly INNER JOIN
dbo.tblBCWSYearly ON dbo.tblBCWSMonthly.RecordId = dbo.tblBCWSYearly.RecordUid INNER JOIN
dbo.tblWBS ON dbo.tblBCWSYearly.WBSUId = dbo.tblWBS.WBSuid

WHERE (dbo.tblWBS.EPSLvl4 = N'1.1.5.17') and dbo.tblWBS.PSWBS = '1.1.5.17.10.01.17001.00000.30 '

GROUP BY dbo.tblWBS.PSWBS, ConcatenatedWBS, CR, NDACost,'01-04-' + ltrim(rtrim(str(FiscalYear))),'30-04-' + ltrim(rtrim(str(FiscalYear))),dbo.tblWBS.wbs

HAVING SUM(dbo.udfBCWScost (NDACost,FiscalYear,Apr_Cost,dbo.tblWBS.PSWBS )) <> 0

UNION ALL

>> Then another 11 select statements|||Check the "Set concat_null_yields_null" setting in your Query Analyzer Connection Properties dialog box. Try toggling it, as it may be set different than your Server default.

No comments:

Post a Comment