Oracle Invalid Identifier Can't Find Field in Subquery

Question

I'm getting the error "ORA-00904: "T"."BORROWER_BALANCE": invalid identifier". It can't find the t.Borrower_Balance field in the inner query and I don't know why. Thanks

SELECT person_id, SSN, Code_Acme_I5, t.Borrower_Balance
FROM 
(
SELECT 
     p.person_id  
    ,p.SSN
    ,MAX(CASE WHEN ld.loan_holder_cd IN ('500','512') THEN ld.loan_holder_cd END) 
  Code_Acme_I5             
  FROM
    nslds_prd.loan@idr l INNER JOIN
    nslds_prd.loan_dtl@idr ld ON l.loan_id = ld.loan_id INNER JOIN
    nslds_prd.person@idr p ON ld.brwr_person_id = p.person_id 
    JOIN (
        SELECT l.brwr_person_id, SUM(l.opb_amt + l.oib_amt) AS Borrower_Balance
        FROM nslds_prd.loan_dtl@idr l           
        GROUP BY l.brwr_person_id
        ) t on t.brwr_person_id = p.person_id 
  WHERE
    p.person_id = 3691451
  GROUP BY
     p.person_id   
    ,p.SSN      

)

Answer 1

Each outer query can only see the immediate sub-query. You cannot reference something nested multiple sub-queries deep.

You may want to move the t sub-query to the outer query:

SELECT g.person_id,
       g.SSN,
       g.Code_Acme_I5,
       t.Borrower_Balance
FROM   (
         SELECT p.person_id,
                p.SSN,
                MAX(
                  CASE WHEN ld.loan_holder_cd IN ('500','512') THEN ld.loan_holder_cd END
                ) AS Code_Acme_I5             
         FROM   nslds_prd.loan@idr l
                INNER JOIN nslds_prd.loan_dtl@idr ld
                ON l.loan_id = ld.loan_id
                INNER JOIN nslds_prd.person@idr p
                ON ld.brwr_person_id = p.person_id 
         WHERE  p.person_id = 3691451
         GROUP BY
                p.person_id,
                p.SSN      
       ) g
       INNER JOIN (
         SELECT l.brwr_person_id,
                SUM(l.opb_amt + l.oib_amt) AS Borrower_Balance
         FROM   nslds_prd.loan_dtl@idr l           
         GROUP BY l.brwr_person_id
       ) t
       ON t.brwr_person_id = g.person_id