Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Full Join Problem
pbewig_at_swbell.net wrote:
> I am using Oracle 9.2.
>
> I am having trouble writing a full join of
> two sub-queries. The join looks like this:
>
> select *
> from (select ...) a
> full join (select ...) b
> on a.xxx = b.xxx
> and a.yyy = b.yyy
>
> This causes an ORA-600 internal error.
>
> I tried rewriting as:
>
> with a as (select ...),
> b as (select ...)
> select *
> from a
> full join b
> on a.xxx = b.xxx
> and a.yyy = b.yyy
>
> but that did nothing (I am running in Toad,
> it returned no error messages and no rows,
> and it returned immediately as I clicked
> F9 to run it, far too quickly for it to
> have done anything).
>
> I was finally able to run my query as:
>
> with a as (select ...),
> b as (select ...)
> select *
> from a
> left join b
> on a.xxx = b.xxx
> and a.yyy = b.yyy
> union all
> select *
> from b
> left join a
> on a.xxx = b.xxx
> and a.yyy = b.yyy
> minus
> select *
> from a
> join b
> on a.xxx = b.xxx
> and a.yyy = b.yyy
>
> I would prefer to use the original full
> join syntax, which is much clearer. Can
> someone explain what is going wrong?
>
> Many thanks,
>
> Phil
ORA-00600 is an unhandled Oracle exception that relates to Oracle rather than your code. If you are on anything less than the latest 9.2.0. patch ... patch before going any further.
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace 'x' with 'u' to respond)Received on Wed Jan 05 2005 - 00:30:50 CST