The interesting thing is why would this work on Oracle 10g and not 9i, had
it not been a bug. I think its a bug since its not necessary to add an extra
select(an extra inline view?) or remove the parens. It does not make sense
to me.
What do you say?
Thanks
Aakash
"Romeo Olympia" <rolympia_at_hotmail.com> wrote in message
news:42fc55dc.0407081933.1baf31ea_at_posting.google.com...
> 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 Fri Jul 09 2004 - 12:05:10 CDT
