Thomas, That makes me feel better atleast. Yes it works on 10g. So I
assume its an Oracle bug. One of our tools is generating the nested
table expression and we dont have control over it.
Thanks
Aakash
Thomas Gaines <Thomas.Gaines_at_noaa.gov> wrote in message news:<cck8aa$fbq$1_at_boulder.noaa.gov>...
> Aakash -
>
> I'm glad that you mentioned your version. I'm running version 9.2.0.3,
> and I received a similar error when executing this query a couple of
> months ago:
>
> select *
> from
> (select table_name, num_rows
> from all_tables where owner = user) a
> full outer join
> (select table_name, num_rows
> from all_tables where owner = user) b
> on a.table_name = b.table_name and
> a.num_rows = b.num_rows;
>
> I took some stabs at tracing this thing, and it appeared as though
> my ORA-942 was appearing on the recursive query
>
> select text from view$ where rowid=:1
>
> While this is not directly related to your case, I'm convinced
> that the use of fully ANSI-standard SQL join syntax is not all
> there in Oracle9i. Interestingly, I was able to execute my query
> in Oracle10g without error, and so I suspect that yours would
> work well, too.
>
> For what it's worth, I found that the use of "inner join", "left
> outer join", "right outer join", and using real tables instead of
> inline views produced good results. It's not what I wanted,
> of course, but it's worth note.
>
> I never took this up with Oracle support because it wasn't a
> huge deal and I had other, more important things to work on.
>
> Thanks,
> TG
>
> Aakash Bordia wrote:
>
> >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:47:43 CDT
