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"

As your question, it's unable to do. Lets try to explain what do you want to do? because you code doesn't clear. My guess is that you want to sum with group by 2 columns for only total_item_value's column but you want to sum with group by 4 columns for other columns. That's right? — Suttipong Pourpawawead, Sep 25 '17 at 06:20

score 0 · Accepted Answer · answered Sep 25 '17 at 07:43

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
;

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

1 Answers1