Re: ORA 1719 outer join operator (+) not allowed in operand of OR or IN

"Jay Canha" <jay.canha_at_yale.edu> wrote in message news:3F1476C1.40DBEFA_at_yale.edu...
> I have recently encountered this error in a query and am trying to
> understand why Oracle doesn't allow this.
>
> For example, the following yields the error:
>
> select A.col1, A.col2, B.col1, B.col2
> from table1 A, table2 B
> where A.col1 = B.col1 (+)
> and (B.col5 (+) = 'X' OR B.col6 (+) = 'Y')
>
> However, this equivalent query with the "or" condition expressed in two
> separate selects which are then unioned, works fine:
>
> select A.col1, A.col2, B.col1, B.col2
> from table1 A, table2 B
> where A.col1 = B.col1 (+)
> and B.col5 = 'X'
> union
> select A.col1, A.col2, B.col1, B.col2
> from table1 A, table2 B
> where A.col1 = B.col1 (+)
> and B.col6 = 'Y';
>
> My query is now running but -- any ideas on *why* Oracle is requiring
> this join to be expressed through two unioned selects, rather than just
> one select?
>
> --
> _________________________________________
>
> Jay Canha
>
>

Those two queries are not equivalent. What exactly are you trying to achieve?  select A.col1, A.col2, B.col1, B.col2
   from table1 A, table2 B
   where A.col1 = B.col1 (+)
   and B.col5 = 'X'

This will effectively negate the usage of outer join on B. You probably want something like this:
 select A.col1, A.col2, B.col1, B.col2
   from table1 A, table2 B
   where A.col1 = B.col1 (+)
   and B.col5 = 'X' (+)

hth
Anurag Received on Tue Jul 15 2003 - 23:17:46 CDT