Re: Outer join bug in Oracle?

Let's take this one step at a time.

Your original (cleaned up) query:

select * from
(

    (select oj1.c1 from oj1 left outer join oj2 on oj1.c1=oj2.c1 ) t1      left outer join
    (select oj2.c1 from oj2 left outer join oj1 on oj1.c2=oj2.c2) t2     on t1.c1=t2.c1
)

doesn't have a SELECT clause in the main/outer inline view. You can rewrite this as:

select * from
(

    select * from /* SELECT clause added to inline view */     (select oj1.c1 from oj1 left outer join oj2 on oj1.c1=oj2.c1 ) t1      left outer join
    (select oj2.c1 from oj2 left outer join oj1 on oj1.c2=oj2.c2) t2     on t1.c1=t2.c1
)

It would be interesting to note that since column names are the same ("C1"), the outer inline view will create system generated column names. Actually, you could finally rewrite this as just:

select * from
(select oj1.c1 from oj1 left outer join oj2 on oj1.c1=oj2.c1 ) t1 left outer join
(select oj2.c1 from oj2 left outer join oj1 on oj1.c2=oj2.c2) t2 on t1.c1=t2.c1

Take a look at the column names now. Just two "C1" columns (not system generated).

HTH. "Aakash Bordia" <a_bordia_at_hotmail.com> wrote in message news:<cck4nh$e1f$1_at_hanover.torolab.ibm.com>...
> Actually you can change this to:
> select * from
> (
> (select oj1.c1 from oj1 left outer join oj2 on oj1.c1=oj2.c1 ) t1
> left outer join
> (select oj2.c1 from oj2 left outer join oj1 on oj1.c2=oj2.c2) t2
> on t1.c1=t2.c1
> )
>
> It wont make a difference, but the query is more cleaner now.
> Thanks
> Aakash
>
> "Aakash Bordia" <a_bordia_at_hotmail.com> wrote in message
> news:cck1fo$dju$1_at_hanover.torolab.ibm.com...
> > Hi , I am trying this on v91. Is this a known issue?
> >
> > select * from
> > (
> > (select * from oj1 left outer join oj2 on oj1.c1=oj2.c1 ) t1
> > left outer join
> > (select * from oj2 left outer join oj1 on oj1.c2=oj2.c2) t2
> > on t1.c1=t2.c1
> > )
> >
> > I get the following error:
> > Associated text and tokens are "ORA-00907: missing right parenthesis ".
> >
> > I am not sure what I am doing wrong. Any clues?
> >
> > Thanks
> > Aakash
> >
> >
Received on Thu Jul 08 2004 - 22:33:39 CDT