surajz surajz - 7 months ago 14
SQL Question

pass an outer selects row variable to inner select in oracle

How do you pass an outer selects row variable to inner select in oracle, here is a sample query ( other outer joins has been removed. This query will be loaded 1 time in life time of an application). This query works

select l5.HIERARCHY_ID,
(select wm_concat(isbn) isbns from (
select op.isbn from oproduct op
LEFT JOIN assignment ha on op.r.reference = ha.reference
where ha.hierarchy_id = '100589'))isbns
from level l5 where l5.gid = '1007500000078694'


but when I change the inner select's where clause

where ha.hierarchy_id = '100589'))isbns
to
where ha.hierarchy_id = l5.HIERARCHY_ID))isbns


I get the following error
ORA-00904: "L5"."HIERARCHY_ID": invalid identifier

Answer

I did something like this to fix the problem. There was one unnecessary select

select  
  l5.HIERARCHY_ID,
  (
    select  
      wm_concat(op.isbn) 
    from 
      oproduct op
      LEFT JOIN assignment ha on op.r.reference = ha.reference
    where ha.hierarchy_id =  l5.HIERARCHY_ID
  ) ISBNS
from 
  level l5 
where 
  l5.gid = '1007500000078694'
Comments