Hive query : Ambiguous column reference acct_nbr in stage

1
votes

I getting "Ambiguous column reference"

query : 

SELECT stage.acct_nbr 
FROM   (SELECT * 
        FROM   mem stage 
               JOIN (SELECT acct_nbr, 
                            corp_ent_cd, 
                            sub_seq_nbr, 
                            mem_nbr, 
                            Max(cdc_src_last_updt_ts) AS cdc_src_last_updt_ts 
                     FROM   mem 
                     WHERE  file_nm = 'DLTV.FULL.MES3191.D180423' 
                     GROUP  BY acct_nbr, 
                               corp_ent_cd, 
                               sub_seq_nbr, 
                               mem_nbr) c 
                 ON c.corp_ent_cd = stage.corp_ent_cd 
                    AND c.acct_nbr = stage.acct_nbr 
                    AND c.sub_seq_nbr = stage.sub_seq_nbr 
                    AND c.mem_nbr = stage.mem_nbr 
                    AND stage.cdc_src_last_updt_ts = c.cdc_src_last_updt_ts 
        WHERE  stage.file_nm = 'DLTV.FULL.MES3191.D180423') stage;

Error Message:

Error: Error while compiling statement: FAILED: SemanticException [Error 10007]: Ambiguous column reference acct_nbr in stage (state=42000,code=10007)

Whereas if I do select * instead of select stage.acct_nbr, it is executing without error.

Could somebody solve my issue?

1
sqlhadoophivehiveql

1 Answers

2
votes

The problem is on SELECT *.

There are two column at subquery c.acct_nbr ,stage.acct_nbr ,so outer SELECT can't stage.acct_nbr distinguish which colunm you want to get.

So you can choose c.acct_nbr or stage.acct_nbr in your select subquery

You can try this.

SELECT stage.acct_nbr 
FROM   (SELECT c.acct_nbr 
        FROM   mem stage 
               JOIN (SELECT acct_nbr, 
                            corp_ent_cd, 
                            sub_seq_nbr, 
                            mem_nbr, 
                            Max(cdc_src_last_updt_ts) AS cdc_src_last_updt_ts 
                     FROM   mem 
                     WHERE  file_nm = 'DLTV.FULL.MES3191.D180423' 
                     GROUP  BY acct_nbr, 
                               corp_ent_cd, 
                               sub_seq_nbr, 
                               mem_nbr) c 
                 ON c.corp_ent_cd = stage.corp_ent_cd 
                    AND c.acct_nbr = stage.acct_nbr 
                    AND c.sub_seq_nbr = stage.sub_seq_nbr 
                    AND c.mem_nbr = stage.mem_nbr 
                    AND stage.cdc_src_last_updt_ts = c.cdc_src_last_updt_ts 
        WHERE  stage.file_nm = 'DLTV.FULL.MES3191.D180423') stage;