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

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 (DOT) com> wrote

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:

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 (DOT) com> wrote

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 (DOT) com> wrote

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 (DOT) gov> wrote