title: ORA-00904: invalid identifier in subquery (in select clause)
body: <p>I have a subquery that needs to use a value from the outer query. It fails because of the notorious "subquery in Oracle can't access a value from a parent query more than two level deeper" issue.</p>
  
  <p>However, I can't figure out how to re-write it. Most examples on the web are for when the subquery is in the WHERE clause; mine is in the SELECT clause.</p>
  
  <p>Help anyone?</p>
  
  <pre><code>select  agre.*, agre.orga_ky,
          orga.NAME_LA_LB as orga_name, 
          pers.LAST_NAME_LA_LB as SIGNATORY_ORGA__LASTNAME, pers.FIRST_NAME_LA_LB as SIGNATORY_ORGA_FIRSTNAME,
          upper(pers.LAST_NAME_LA_LB) || ' ' || pers.FIRST_NAME_LA_LB as SIGNATORY_ORGA_FULLNAME,
          -- Get the most current agreement for this orga and compare it with this row to find out if this row is current or expired
          CASE WHEN (
              SELECT AGRE_KY
              FROM (
                  SELECT a.AGRE_KY
                  FROM T_AGREEMENT a
                  WHERE a.ORGA_KY = agre.orga_ky -- fail!!! ORA-00904: invalid identifier
                  ORDER BY a.REC_CREATION_DT DESC
              )
              WHERE ROWNUM = 1
          ) = agre.agre_ky THEN 'Current' ELSE 'Expired' END as agreement_status
  from T_AGREEMENT agre
  left outer join T_ORGANIZATION orga on agre.orga_ky = orga.orga_ky
  left outer join T_PERSON pers on agre.SIGNATORY_ORGA_PERS_KY = pers.pers_ky
  ;
  </code></pre>