I don't believe that your example properly shows the problem.
I ran the following script and received a proper resultset:
That is because you are outer-joining (+) each table only once.Code:create table a (id number); create table b (id number); create table c (id number); create table d (id number); create table e (id number); create table f (id number); insert into a values (1); insert into b values (1); insert into c values (1); insert into d values (1); insert into e values (1); insert into f values (1); select * from a, b, c, d, e, f Where a.id = b.id (+) and b.id = c.id (+) and a.id = d.id (+) and d.id = e.id and a.id = f.id (+)
Note that the following SQL will produce the error:
It is very rare that you will need to outer-join to more than one table. In such cases, you will need to, as you stated, re-write the query to put the two tables that you wish to outer-join to into an inline view and then outer-join to the results.Code:select * from a, b, c, d, e, f Where a.id = b.id (+) and b.id = c.id (+) and a.id = d.id (+) and e.id = d.id(+) and a.id = f.id (+)
I would first check the logic of your statement, because it is more likely that there is a logical error in what you are trying to do.
Two more points of note from your original example, which may just be because you threw it together:
d.id = e.id <-- This will eliminate what you tried to accomplish in the previous predicate:
a.id = d.id (+)
Also note that if all these tables shared the same ids, then the best way to write the WHERE clause would be:
b.id (+)= a.id AND
c.id (+)= a.id AND
d.id (+)= a.id AND
e.id (+)= a.id AND
f.id (+)= a.id
This gives the optimizer more options and more correctly reflects what you are trying to do, assuming a is the driving table and the rest are children of a.




Reply With Quote