Result Differences in sql 2000 and sql 2008

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Result Differences in sql 2000 and sql 2008

Author

Topic

Ancy
Starting Member

23 Posts

Posted - 2010-09-24 : 06:22:46

Hi
I have a stored procedre that needs to be upgraded from SQL 2000 to sql 2008. One of te queries had to be changed from "*=" syntax to left outer join syntax for the upgradation . However after making the changes, the results are differing between the two queries

SQL 2000 Query

select u.U_DED,k.K_DED,um.NEXT_AMOUNT,km.NEXT_AMOUNT,
yk.YTD_K_DED, yk.YTD_K_DED * .4 YTD_MATCH
from
#HOUR h,
#401U u,
#401K k,
#401U_MATCH um,
#401K_MATCH km,
#YTD_K yk
where h.EMPLOYEE *= um.EMPLOYEE
and h.EMPLOYEE *= km.EMPLOYEE
and h.EMPLOYEE *= u.EMPLOYEE
and h.EMPLOYEE = k.EMPLOYEE
and h.EMPLOYEE *= yk.EMPLOYEE
and (um.NEXT_AMOUNT <> 0 and km.NEXT_AMOUNT <> 0 and u.U_DED <> 0 and k.K_DED <> 0)

Result
------
U_DED K_DED NEXT_AMOUNT NEXT_AMOUNT YTD_K_DED YTD_MATCH
----- ----- ----------- ----------- --------- ---------
NULL 20.00 NULL NULL 20.00 8.000

SQL 2008 Query

select u.U_DED,k.K_DED,um.NEXT_AMOUNT,km.NEXT_AMOUNT,
yk.YTD_K_DED, yk.YTD_K_DED * .4 YTD_MATCH
from #HOUR h
LEFT OUTER JOIN #401U_MATCH um ON h.EMPLOYEE = um.EMPLOYEE
LEFT OUTER JOIN #401K_MATCH km ON h.EMPLOYEE = km.EMPLOYEE
LEFT OUTER JOIN #401U u ON h.EMPLOYEE = u.EMPLOYEE
JOIN #401K k ON h.EMPLOYEE = k.EMPLOYEE
LEFT OUTER JOIN #YTD_K yk ON h.EMPLOYEE = yk.EMPLOYEE
and (um.NEXT_AMOUNT <> 0 and km.NEXT_AMOUNT <> 0 and u.U_DED <> 0 and k.K_DED <> 0)

Result

U_DED K_DED NEXT_AMOUNT NEXT_AMOUNT YTD_K_DED YTD_MATCH
----- ----- ----------- ----------- --------- ---------
NULL 20.00 NULL NULL NULL NULL

How can I get the same result as the sql 2000 query in sql 2008?

Thanks
Ancy

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-24 : 06:30:01

your last line " and (um.Next_Amount ...)"
has to be " WHERE (um.Next_Amount ...)"
because it is NOT a part of an ON Clause.

No, you're never too old to Yak'n'Roll if you're too young to die.

Ancy
Starting Member

23 Posts

Posted - 2010-09-24 : 06:44:15

Hi thanks for the reply but if i add it as a part of the where clause, the whole result record is eliminated.

I have found the solution though..
the sql 2008 query should be

select u.U_DED,k.K_DED,um.NEXT_AMOUNT,km.NEXT_AMOUNT,
yk.YTD_K_DED, yk.YTD_K_DED * .4 YTD_MATCH
from #HOUR h
LEFT OUTER JOIN #401U_MATCH um ON h.EMPLOYEE = um.EMPLOYEE
JOIN #401K k ON h.EMPLOYEE = k.EMPLOYEE
LEFT OUTER JOIN #401K_MATCH km ON h.EMPLOYEE = km.EMPLOYEE
LEFT OUTER JOIN #401U u ON h.EMPLOYEE = u.EMPLOYEE
and u.U_DED <> 0 and k.K_DED <> 0
and um.NEXT_AMOUNT <> 0 and km.NEXT_AMOUNT <> 0
LEFT OUTER JOIN #YTD_K yk ON h.EMPLOYEE = yk.EMPLOYEE

had to do with the order of execution

Kristen
Test

22859 Posts

Posted - 2010-09-24 : 06:44:51

I think those individual tests for <> 0 need to be put in the JOIN for each table that they refer to - otherwise they will force an inner join.

Surprised it ever worked the "old way"!!

Ancy
Starting Member

23 Posts

Posted - 2010-09-24 : 07:56:25

Thanks for pointing it out...That seems to be the proper way of doing it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources