sql - AnalysisException: duplicated inline view column alias: 'account_id' in inline view 'q2' -

select *

from (

     select * 
     from prod_ard119_master.ead_basel_exp_id_mdp as crmd_ead
     inner join prod_account_app.account_customer as crmd_acc
     on crmd_ead.account_id = crmd_acc.account_id)  
as q2

inner join

prod_ard119_master.lgd_basel_exp_id_mdp 

on prod_ard119_master.lgd_basel_exp_id_mdp.customer_id = q2.customer_id

This is the error:

AnalysisException: duplicated inline view column alias: 'account_id' in inline view 'q2'

select *

from (

     select * 
     from prod_ard119_master.ead_basel_exp_id_mdp as crmd_ead
     inner join prod_account_app.account_customer as crmd_acc
     on crmd_ead.account_id = crmd_acc.account_id)  
as q2

inner join

prod_ard119_master.lgd_basel_exp_id_mdp 

on prod_ard119_master.lgd_basel_exp_id_mdp.customer_id = q2.customer_id

This is the error:

AnalysisException: duplicated inline view column alias: 'account_id' in inline view 'q2'

• sql
• hadoop
• inner-join
• impala

Share Improve this question Follow edited Jan 16 at 11:44 samhita 4,12522 gold badges1111 silver badges1818 bronze badges asked Jan 16 at 11:35 AlketCecajAlketCecaj 11711 silver badge1111 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

I dont have hadoop to run, but from the looks of it , it seems like it is caused because of column account_id being present both in q2 and in prod_ard119_master.lgd_basel_exp_id_mdp . So your outer select * does not really know which account_id to choose from.

In your q2 subquery , you can specify the column names

select crmd_ead.<col_name> , crmd_acc.<col_name>
     from prod_ard119_master.ead_basel_exp_id_mdp as crmd_ead
     inner join prod_account_app.account_customer as crmd_acc
     on crmd_ead.account_id = crmd_acc.account_id

And similarly in outer select you can do the same

select q2.<col_name> , mdp.<col_name>

from (

     select crmd_ead.<col_name> , crmd_acc.<col_name> 
     from prod_ard119_master.ead_basel_exp_id_mdp as crmd_ead
     inner join prod_account_app.account_customer as crmd_acc
     on crmd_ead.account_id = crmd_acc.account_id)  
as q2

inner join

prod_ard119_master.lgd_basel_exp_id_mdp mdp

on mdp.customer_id = q2.customer_id

Note : Its not a good practice to use SELECT *, instead you can specify the columns in SELECT to avoid such issues.