CODEWITHSUNDEEP
sqloracle-databaseselectsubqueryalias
Mohammad Jalal Ahmadzai
Mohammad Jalal Ahmadzai

Reputation: 31

table alias not work in subquery in oracle

I am generating records with sum aggregate function and subquery, but the alias is not work there in inner query. my query is 

    select UPP.item_total, 
           (select sum(INN.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))    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 this error : ORA-00904: "UPP"."TPT_CUO_NAM": invalid identifier

I want like this result!!!

enter image description here

Upvotes: 0

Views: 2623

Answers (2)

ulferts
ulferts

Reputation: 2242

Your innermost subquery 

(select distinct nn.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
)

references a table that is not joined (upp). It also does not have an alias but that problem would come later. Please note, that there also seems to be a type nn.reg_no instead of inn.reg_no

The structure of the tables is not displayed here but fixing the problem would mean something along the lines of:

(select distinct inn.reg_no,inn.tpt_cuo_nam, inn.item_total, inn.item_value_afs
 from sigtasad.customs_import_data inn, SIGTASAD.CUSTOMS_IMPORT_DATA upp
 where inn.reg_no = upp.reg_no and inn.tpt_cuo_nam = upp.tpt_cuo_nam
)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271111

Your query has numerous errors and bad habits. For instance:

  • You qualify a column name with an undefined table alias.
  • You are aggregating by columns not in the select.
  • You are using sum() on a subquery that has sum().

Based on the picture that you show, you probably want something like this:

select upp.item_total,
       sum(item_value_afs) as 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.cmp_nam, upp.item_total ;

Or perhaps:

select upp.item_total,
       sum(sum(item_value_afs)) over (partition by upp.cmp_nam, upp.item_total) as 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.cmp_nam, upp.item_total ;

Upvotes: 0

Related Questions