lundi 9 avril 2018

Compare this two columns with different format in hive

I have source table: b_ACCOUNT.b_curreny and target table account.currency. The source table b_ACCOUNT.b_curreny currency format is 'c' and 'U' and the target table account.currency format is ‘CAD’ and ‘USD’

I use case to change 'c' and 'U' format to 'CAD' and 'USD' and compare this two columns. I use the following query and this query is still compared target column acc.CURRENCY (CAD) with the source column b.b_curreny(c)'

SELECT count(*) count ,

case WHEN b_curreny ='C' THEN 'CAD'

WHEN b_curreny ='U' THEN 'USD'

ELSE NULL END

FROM ACCOUNT acc

join b_ACCOUNT b on acc.account_NUMBER=b.account_NUMBER

and acc.CURRENCY <> b.b_curreny

If I use the following query, I get error message Invalid table alias or column reference 'souce_currency':

SELECT count(*) count ,

case WHEN b_curreny ='C' THEN 'CAD'

WHEN b_curreny ='U' THEN 'USD'

ELSE NULL END assource_curreny

FROM ACCOUNT acc

join b_ACCOUNT b on acc.account_NUMBER=b.account_NUMBER

and acc.CURRENCY <> source_curreny

Could you help me to compare these two columns with a different format?

Aucun commentaire:

Enregistrer un commentaire