Re: Outer join

HI,

Try these:

1. Use MINUS instead

Select X,Y,X
from ...
where ... = 'B'
GROUP BY X,Y,X
MINUS
Select X,Y,X
from ...
where ... = 'A'
GROUP BY X,Y,X 2)

Use a virtual table in the FROM clause, and join to it:

SELECT X, Y, Z
from
(
SELECT cd_name
FROM
cil_displays, cil_user_displays
WHERE cud_cu_id = 'USER_B'
AND cud_cd_sys_id = cd_sys_id
) v_table,
cil_DISPLAYS
WHERE CIL_DISPLAYS.FIELD1 = V_TABLE.field1 (+)

Good luck,

Argosy

In article <85007e$6pj$1_at_nnrp1.deja.com>,   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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jan 05 2000 - 15:26:20 CST