Re: Outer Join problem

Greg,

"ORA-01799: a column may not be outer-joined to a subquery".

Either use an outer join with a JOIN syntax, or use a subquery with a correlated subquery syntax, but not both....

Examples.
(want User_B has, but User_A does not):

• Correlated subselect ***

SELECT a.cd_Name
FROM cil_displays a, cil_user_displays aa WHERE
  aa.cud_cu_id = 'USER_B'
 AND aa.cud_cd_sys_id = a.cd_sys_id
 AND NOT EXISTS
    (SELECT 'x'
      FROM cil_displays b, cil_user_displays bb     WHERE bb.cud_cu_id = 'USER_A'
    AND bb.cud_cd_sys_id = b.cd_sys_id
    AND a.cd_name = b.cd_name)

• outer join method *** Select cd_name From cil_displays a, cil_user_displays aa, cil_displays b, cil_user_displays bb Where aa.cud_cu_id = 'USER_B' And bb.cud_cu_id = 'USER_A' And aa.cud_cd_sys_id = a.cd_sys_id And bb.cud_cd_sys_id = b.cd_sys_id And a.cd_name = b.cd_name (+) And b.cd_sys_id IS NULL -- test for no B records returned.

... anyway, somthing like that.
Try a test on sample tables first, you may or may not need more outer join operators (+)....

Good Luck,
Robert Proffitt

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jan 06 2000 - 01:50:18 CET