Re: Too Many Full Outer Joins??? PL/SQL: ORA-00918: column ambiguously defined

"Joe" <joemitchellsc_at_yahoo.com> wrote in message news:1126742657.132231.278850_at_g43g2000cwa.googlegroups.com...
>I have about 10 queries that I'm stacking together as follows:
>
> select *
> from (select a, b from q1) q1
> full outer join (select a, b from q2) q2 ON q1.a = q2.a
> full outer join (select a, b from q3) q2 ON q1.a = q3.a
> full outer join (select a, b from q4) q2 ON q1.a = q4.a
> full outer join (select a, b from q5) q2 ON q1.a = q5.a
> full outer join (select a, b from q6) q2 ON q1.a = q6.a;
>
> When I try to compile them into a package, on the 7th query, I get this
> error: PL/SQL: ORA-00918: column ambiguously defined
>
> I've removed the sub-queries one at a time and re-inserted them in
> different orders and they work until the 7th one is added.
>
> I have no problems when I change the full outer joins to left joins;
> however I need to use full outer joins to get the correct data back.
>
> Any ideas?
>
> Thanks,
> Joe
>

It seems likely you are hitting bug 4199351 - which is about too many left outer joins. Workarounds - use fewer outer joins, or
stop using ANSI syntax.

See also thread "Outer join - same query different results" dated 18th Aug on this newsgroup for further details.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle - Volume 1: Fundamentals
On-shelf date: Nov 2005

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Sept 2005