Home -> Usenet -> comp.databases.oracle -> Select query Works in Oracle Not MS-SQL Fix??

Select query Works in Oracle Not MS-SQL Fix??

From: Peter MacDonald <peter.macdonald_at_creatcomp.com>
Date: 17 Jan 2002 07:52:58 -0800
Message-ID: <ca76de4e.0201170752.2a6f2681_at_posting.google.com> 






I have 2 tables


Emp                   TSDate
Emp#  Fname           Emp#  Week_Ending
1     Dave            1     01/04/2002
2     Denise          2     01/04/2002
3     Mary            3     01/04/2002
4     Pete            2     01/11/2002
                      3     01/11/2002





In Oracle I would right the query as follows:



SELECT FNAME, WEEK_ENDING



FROM Emp, TSDate


WHERE Emp.Emp# = TSDate.Emp# (+) AND

      (Week_Ending = 01/11/2002 OR
       Week_EnDing IS NULL)




Order by Emp.Emp#



Results in


Fname       Week_Ending
Dave        null
Denise      01/11/2002
Mary        01/11/2002
Pete        null





If I translate this to MS-SQL Server 7.0 as:



SELECT FNAME, WEEK_ENDING



FROM (Emp left outer join TSDate 


       On  Emp.Emp# = TSDate.Emp#)


WHERE (Week_Ending = 01/11/2002 OR


       Week_EnDing IS NULL)


Order by Emp.Emp#



I get as results:


Fname       Week_Ending
Denise      01/11/2002
Mary        01/11/2002
Pete        null





How could I get this to work?



Thanks,


Pete
Received on Thu Jan 17 2002 - 16:52:58 CET












Original text of this message