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

"Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:Kl4Ra.4518$zt1.2812_at_news02.roc.ny...
>
> "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
>
>

oops .. I wrote wrong.
I meant

Try this:
  select A.col1, A.col2, B.col1, B.col2
    from table1 A, table2 B
    where A.col1 = B.col1 (+)
    and B.col5 (+) = 'X'

.. and yes oracle does not allow outer join on or/in

You might be looking for something like: select A.col1, A.col2, B.col1, B.col2
 from table1 A, (select col1, col2 from table2 where col5 = 'X' or col5 = 'X') B  where A.col1 = B.col1 (+)

hth
Anurag Received on Tue Jul 15 2003 - 23:27:13 CDT