full outer join on 8i

From: <utefan001_at_gmail.com>
Date: 19 Jul 2006 05:04:04 -0700
Message-ID: <1153310644.863286.172470@h48g2000cwc.googlegroups.com>

On 8i, I have some rows that are not returning in a select query. The sql is much bigger then this, but the basic problem is....

select *
from
tableA a,
tableB b,
tableC c
where a.someID = b.id(+)
and a.someID = c.id(+)
and a.ref like '%4E%'

Now keep in mind that (in this rare case) a.someID is null where a.ref like '%4E%'
This is a sort of "triple check" finance report to ensure certain project budget values have not been missed.

To get the row to return in 8i, I was hoping to apply what I read here...
http://www.oreillynet.com/pub/a/network/2002/04/23/fulljoin.html

Note that the "(+)" is moved to the other side in the second select below...

---

select *
from
tableA a,
tableB b,
tableC c
where a.someID = b.id(+)
and a.someID = c.id(+)
and a.ref like '%4E%'
UNION ALL
select *
from
tableA a,
tableB b,
tableC c
where a.someID(+) = b.id
and a.someID(+) = c.id
and a.ref like '%4E%'

---

The error I get is ORA-01417
A table may be outer joined to at most one other table

Be nice ; ) Received on Wed Jul 19 2006 - 07:04:04 CDT