full outer join on 8i

utefan001@gmail.com · *nix forums beginner Joined: 11 Jul 2006 Posts: 4

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 ; )

Brian Peasland · *nix forums Guru Joined: 04 Apr 2006 Posts: 301

Full outer joins were not implemented until Oracle 9i (a good reason to
upgrade that database?). Since you are using pre-9i, you will have to
rewrite the query to use a UNION of two OUTER JOINs, similar to the
following:

SELECT a.col1,a.col2,b.colA,b.colB
FROM tableA a, tableB b
WHERE a.someID = b.id (+)
AND a.ref LIKE '%4E%'
UNION
SELECT a.col1,a.col2,c.colA,c.colB
FROM tableA a, tableC c
WHERE a.someID = c.id (+)
AND a.ref LIKE '%4E%';

It looks like your UNION query is not written correctly.

HTH,
Brian

--
===================================================================

Brian Peasland
dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.

"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Google