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

Chris,

Sorry I was not clear with my first question. the first result set that I showed is what I need to generate with MS-SQL server. I think I see what needs to be done I need to include the date filter in side of the inner join. I'll need to test.

thanks,
Pete

christianboivin1_at_hotmail.com (Chris) wrote in message news:<da20daf0.0201221407.1d9dc195_at_posting.google.com>...
> 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)
> Order by Emp.Emp#
>
> 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 29 2002 - 21:07:17 CET