error : currently subquery expressions are only allowed as where clause predicates in hive

Question

I have written a hive query language as below. It is giving me error as written in title. the query is :

SELECT clnt_nbr,
       CASE
           WHEN clnt_nbr in
                  (SELECT clnt_NBR
                   FROM crd_master
                   WHERE crd_typ = '198' or crd_typ = '199' ) THEN 1
           ELSE 0
       END) AS f_ultima
FROM INT_CLNT_master
ORDER BY clnt_nbr;

I am getting error in this hive query as

"currently subquery expressions are only allowed as where clause predicates in hive".

Answer 1

Please rewrite the hive ql like below using ansi join syntax. If you see duplicates, please use your subqry and use distinct.

select icm.clnt_nbr, 
case when cm.clnt_nbr is not null then 1 else 0 end as f_ultima 
from INT_CLNT_master icm 
Left outer join crd_master cm on icm.clnt_nbr=cm.clnt_NBR and ( crd_typ = '198 or crd_typ = '199' )
order by clnt_nbr;