title: pass an outer selects row variable to inner select in oracle
body: <p>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</p>
  
  <pre><code>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'
  </code></pre>
  
  <p>but when I change the inner select's where clause</p>
  
  <pre><code>where ha.hierarchy_id =  '100589'))isbns 
  to
  where ha.hierarchy_id =  l5.HIERARCHY_ID))isbns  
  </code></pre>
  
  <p>I get the following error
  ORA-00904: "L5"."HIERARCHY_ID": invalid identifier</p>