Re: Outer Join to a subquery alternative?

From: kibeha <kibeha_at_post6.tele.dk>
Date: 9 Feb 2004 01:08:16 -0800
Message-ID: <444b180d.0402090108.27fbda4f@posting.google.com>

> > John wrote:
> > 

> >> Oracle doesnt like
> >>
> >> select whatever
> >> from maintable m,
> >> childtable c
> >> where c.seq(+)=(select max(seq) from childtable c where
> >> maintableseq=m.seq)

In-line view IS the way to get the result you want :

select m1.whatever, c.whatever
from (

   select m.whatever,

      nvl((select max(seq) from childtable c
           where maintableseq=m.seq),0) childmaxseq

   from maintable m
   where m.something = something
) m1, childtable c
where c.seq(+) = m1.childmaxseq

In the inner query you have your where clause on maintable and you take care of selecting the max seq number for the child. nvl is used to make sure childmaxseq does not contain a null. If you have the possibility of seq=0, then replace the zero in my example with something seq can never be (-1 for example).

KiBeHa Received on Mon Feb 09 2004 - 03:08:16 CST