Outer join

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 - 11:49:40 CST