Re: AND, OR can not be used in an outer join condition?

gmei_at_my-deja.com wrote in message <8vv8me$sl9$1_at_nnrp1.deja.com>...
>I am trying to understand the following question:
>
>Q: Which two operators can not be used in an outer join condition?
>
>a. =
>b. IN
>c. AND
>d. OR
>
>Answer is c and d.
>
>I understand "=" can be used in outer join condition. Could some one give
>examples of how to use "IN" in an outer join and what kind of error "AND"
>and "OR" would generate in an outer join condition?

The answer is wrong, or at least, ambiguous:

The statement (using the scott/tiger demo database):

    select d.deptname, e.ename

      from dept d,
           emp e
     where d.deptno(+) = e.empno
       and d.deptno(+) = e.empno + 1;

is perfectly valid syntax (although completely useless!) and does not give an error. (You can easily come up with useful examples using more relaistic tables). But replace "and" with "or" and you get the error message:

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

And Yong gave an example where "in" is valid!

So the "book" answer, using the above error message to guide you, is "b and d", and the real answer, using Yong's example is "d only".

Dave.

--
Remove "nospam" from my email address to reply to this newsgroup posting
by email