Re: ORA-01417: a table may be outer joined to at most one other table

Christoph Seidel wrote:

> Hi,
>
> could someone plz explain why this restriction exists. I can workaround it
> by creating a view with the first outer join and then outer join with this
> view. Shouldn't this be the same like doing it all in one statement? It
> seems that my workaround produces the right results. Are there any very bad
> performance impacts when using this workaround?
>
> My data:
>
> table A and B: about 100 rows
> table C: about 10 million rows
>
> outer joins:
>
> A = B(+)
> and
> C = B(+)

The restriction exists as you have coded it but it does not exist if coded correctly.

SELECT *
FROM a, b,

   (SELECT *
    FROM b, c
   WHERE c.col1 = b.col1(+)) x
WHERE a.col1 = b.col1 (+)
AND a.col1 = x.col1;

Just use an in-line view.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
http://www.outreach.washington.edu/extinfo/certprog/aoa/aoa_main.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)