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?

Apparently your OR has been automatically translated to IN, using the inlist iterator, that is if you are using 8i or higher, which you don't mention. You could easily confirm this by running explain plan on the statement.
To avoid the inlist iterator, apply the /*+ USE_CONCAT */ hint in your statement, that is when you are using CBO.

Regards

Sybrand Bakker
Senior Oracle DBA Received on Wed Jul 16 2003 - 02:57:54 CDT