Different Results With View Versus UDF

I have two views, for various reasons I decided to wrap a Select * From View with two UDFs. When run on their own, the UDFs return the exact same resultset as their respective view (did a compare and stuff to make sure). However, when I join them together, what was once 130,000 records (when joining the views) skyrockets into millions of records, and the resulting resultset is filled with duplicates. What is going on?Firstly I would use a stored procedure to return result sets, not UDF's. Secondly, it looks like you are getting a cartesian join, where every row in one table joins to many or every row in the other table. This is caused by incorrectly joining the two table (or views). This is illustrated by the duplicate rows you are getting. Check your joins, and make sure you join primary key to foreign key. To test the query, change the select list to a select count(*), and then run the query with just the first join condition. Then add each condition one by one. At some point the rowcount will explode. However, if the first join produces a massive row count, experiment with adding additional joins until you get the correct amount of records returned.

HTH

For more SQL tips, check out my blog:|||The exact same join with the exact same conditions using the views (which return an identical result set) returns the proper result.|||that's weird, you could have been executing a cross join|||You will have to post a repro script to demonstrate the problem. Otherwise it will be hard to guess what might be wrong. Your SELECT statement could be incorrect, the UDF could be wrong and so on.

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.