Re: Outer join

Without digging too deep into how you are doing it, but focussing in on what you want to do, why don't you check out the Set Operators (INTERSECT, MINUS, UNION, & UNION ALL) to see if those will get you what you want..

cindy

lechkung_at_dteenergy.com wrote:

> Help for an outer join (I think)!
>
> I'm trying to select from a table the display names of USER_A doesn't
> have but USER_B does have. To get a list of displays that both USER_A
> and USER_B has I have the following select statement:
>
> SELECT cd_name
> FROM cil_displays, cil_user_displays
> WHERE
> cud_cu_id = 'USER_A' AND
> cud_cd_sys_id = cd_sys_id AND
> cd_name IN ( SELECT cd_name FROM
> cil_displays, cil_user_displays
> WHERE
> cud_cu_id = 'USER_B' AND
> cud_cd_sys_id = cd_sys_id )
>
> When using the above statement I get one cd_name back (which is
> correct).
>
> Now, to get a list of cd_name's back that USER_B has but not USER_A, I
> want to use an outer join like this:
>
> SELECT cd_name
> FROM cil_displays, cil_user_displays
> WHERE
> cud_cu_id = 'USER_A' AND
> cud_cd_sys_id = cd_sys_id AND
> cd_name (+) IN ( SELECT cd_name FROM
> cil_displays, cil_user_displays
> WHERE
> cud_cu_id = 'USER_B' AND
> cud_cd_sys_id = cd_sys_id )
>
> Except, that I get "ORA-01799: a column may not be outer-joined to a
> subquery".
>
> Can anyone help me?!
>
> Greg Lechkun
> lechkung_at_dteenergy.com
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Jan 05 2000 - 16:09:43 CST