Re: Outer joins with Operands of OR

From: David R. Thrash <thrash_at_sbctri.sbc.com>
Date: 1 Jul 1994 13:55:42 GMT
Message-ID: <2v178u$lkr_at_sbctri.sbc.com>


In article <773007969snz_at_aboy.demon.co.uk> MarkB_at_aboy.demon.co.uk (M a r k) writes:
> In Version 7 the following will give an error:-
>

 [edited]
>

select *
  from tabA a, tabB b

 where a.fld1 = b.fld1(+)
   and b.fld2 is null
    or a.fld2 	= 'ANYTHING YOU LIKE'

>
> the error being something not unakin to:
>
> Cannot use outer join in operand of OR or IN.
>

[chomp]

First, try from the command prompt "oerr ora 1234" where 1234 is the error number. This gives a description of the problem AND a suggested action/solution. I don't currently have access to v7, so I cann't site specifics.

Second, I THINK this is one time when version V6 is actually WRONG. This means no errors and wrong results. In V7 oracle "fixed" this query by returning an error.

Really what you are asking from the database, as is more apparent from my edited select statment is:

select *
  from tabA a, tabB b
 where a.fld1 = b.fld1(+)
   and b.fld2 is null
 union all
select *
  from tabA a, tabB b
 where a.fld2 = 'ANYTHING YOU LIKE'

This is the solution suggested by "oerr". Of course the "all" is not needed unless you have duplicate rows, in which case you do need "psychiatric help".

Good luck,

drt Received on Fri Jul 01 1994 - 15:55:42 CEST

Original text of this message