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

Hi
I try it with ORACLE like this

SELECT FNAME, WEEK_ENDING
FROM Emp, TSDate
WHERE Emp.Emp# = TSDate.Emp# (+) AND

      (Week_Ending = to_date('01/11/2002','dd/mm/yyyy') OR
       Week_EnDing IS NULL)

and my result :

Denise	2002-11-01
Mary	2002-11-01
Pete	null

the same then SQL Server
the only thing i change is the to_date because i assume that the type of week_ending is Date

is it correct or you want

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

you can get it with this query

SELECT FNAME, WEEK_ENDING
FROM Emp, TSDate
WHERE Emp.Emp# = TSDate.Emp#(+) AND

      Week_Ending(+) = to_date('01/11/2002','dd/mm/yyyy') Order by Emp.Emp#

hth
Chris

peter.macdonald_at_creatcomp.com (Peter MacDonald) wrote in message news:<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 Tue Jan 22 2002 - 23:07:54 CET