Re: Outer join bug in Oracle?

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 Thu Jul 08 2004 - 14:47:20 CDT