Full Join Problem

From: <pbewig_at_swbell.net>
Date: 4 Jan 2005 06:55:47 -0800
Message-ID: <1104850547.006188.166560@c13g2000cwb.googlegroups.com>

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 Received on Tue Jan 04 2005 - 08:55:47 CST