ORA-00904: invalid identifier in subquery (in select clause)

Question

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.

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.

Help anyone?

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
;

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production — Junglefish

Vamsi Prabhala Vamsi Prabhala · Accepted Answer · 2016-07-19T13:19:08

You can try using the row_number window function without the sub-query and check if it returns the expected result.

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 row_number() over(partition by agre.orga_ky order by agre.REC_CREATION_DT desc)   
                        ----------------^^^^^^^^^^^^ change the partitioning column per your requirement
= 1 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

ORA-00904: invalid identifier in subquery (in select clause)

1 Answers