how to use upper query table alias in inner query in subquery

Question

select upp.item_total,
   (select sum(iva.total_item_value_afs)
    from (select sum(item_value_afs) total_item_value_afs 
          from (select distinct inn.reg_no,inn.tpt_cuo_nam, inn.item_total, inn.item_value_afs
                from sigtasad.customs_import_data inn
                where inn.reg_no = upp.reg_no and inn.tpt_cuo_nam = upp.tpt_cuo_nam
               )
         ) iva
   ) total_item_value,
   sum(upp.code_tax_amount),
   upp.cmp_nam from SIGTASAD.CUSTOMS_IMPORT_DATA upp where upp.reg_no = '38699' and upp.company_tin = '9003247336' group by upp.reg_no, upp.tpt_cuo_nam, upp.cmp_nam, upp.item_total ;

this query generate bellow error:

ORA-00904: "UPP"."TPT_CUO_NAM": invalid identifier 00904. 00000 - "%s: invalid identifier"

Answer 1

Try joining a "derived table" instead of a complex "correlated subquery" that employs "select distinct". Without any sample data etc. it's something of a guess but it may look more like this:

SELECT
      upp.reg_no
    , upp.tpt_cuo_nam
    , upp.cmp_nam
    , upp.item_total
    , d.total_item_value
    , SUM(upp.code_tax_amount)
FROM sigtasad.customs_import_data upp
LEFT JOIN (
            SELECT
                  inn.reg_no
                , inn.tpt_cuo_nam
                , SUM(iva.total_item_value_afs) total_item_value
            FROM sigtasad.customs_import_data inn
            GROUP BY
                  inn.reg_no
                , inn.tpt_cuo_nam
            ) d ON upp.reg_no = d.reg_no
                  AND upp.tpt_cuo_nam = d.tpt_cuo_nam
WHERE upp.reg_no = '38699'
AND upp.company_tin = '9003247336'
GROUP BY
      upp.reg_no
    , upp.tpt_cuo_nam
    , upp.cmp_nam
    , upp.item_total
    , d.total_item_value
;