Home » SQL & PL/SQL » SQL & PL/SQL » Old Oracle standard and ANSI standard help (Oracle 10g R2)
Old Oracle standard and ANSI standard help [message #282630] Thu, 22 November 2007 13:56 Go to next message
hungman
Messages: 16
Registered: September 2006
Junior Member
Hi guys,

I used to code my joins under the old Oracle standard way, eg.

select    1
from      table_A,
          table_B
where     table_A.column1 = tableB.column1


but now I'm switching to the ANSI standard, eg.
select    1
from      table_A
          inner join table_B on table_B.column1 = table_A.column1


the problem I'm having is attempting an outer join (using ANSI) on a subquery.

select     column1,
           column2
from       table_A
           left outer join table_B on table_B.column1 = table_A.column1
           left outer join table_C on table_C.column2 = table_A.column2
           and table_C.date = (select max(date)
                               from   table_D
                               where  table_D.column3 = '1'
                              )
where      table_A.column4 = 'New'


can somebody give me a hand on to properly write this query in ANSI standard kinda way?

Thanks,
John.
Re: Old Oracle standard and ANSI standard help [message #282643 is a reply to message #282630] Thu, 22 November 2007 20:40 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
A sub-query is a filter, not a join condition. Use it in the WHERE clause.

select     column1,
           column2
from       table_A
           left outer join table_B on table_B.column1 = table_A.column1
           left outer join (
                select *
                from   table_C
                where  table_C.date = (select max(date)
                               from   table_D
                               where  table_D.column3 = '1'
                               )
            ) table_C on table_C.column2 = table_A.column2
where      table_A.column4 = 'New'


Ross Leishman
Previous Topic: Dynamic variable length
Next Topic: A Syntax issue, whats wrong?
Goto Forum:
  


Current Time: Sun Feb 09 21:31:49 CST 2025