Hive3 中case when的一个乱码问题与实践
现象描述:
select a.apply_no, (case when coalesce(a.final_repay_date, '2999-12-31') > '2020-05-01' then a.uid when coalesce(a.user_final_repay_date, '2999-12-31') > '2020-05-01' then a.uid else 'null' end) as aa from dwm_asset_user_detail_comm_day a where apply_no = '20191125004334' 或者 select a.apply_no, (case when coalesce(a.user_final_repay_date, '2999-12-31') > '2020-05-01' then a.uid when coalesce(a.user_final_repay_date, '2999-12-31') > '2020-05-01' then a.uid else 'null' end) as aa from dwm_asset_user_detail_comm_day a where apply_no = '20191125004334'
以上代码如果 coalesce(a.user_final_repay_date, '2999-12-31') > '2020-05-01' 和 coalesce(a.final_repay_date, '2999-12-31') > '2020-05-01' 条件同时被满足即输出乱码
但是在以下情况下不会出现乱码,虽然when的两个条件同时被满足了:
select a.apply_no, (case when 1=1 then a.uid when 2=2 then a.uid else 'null' end) as aa from dwm_asset_user_detail_comm_day a where apply_no = '20191125004334' 或者 select a.apply_no, (case when coalesce(a.user_final_repay_date, '2999-12-31') > '2020-05-01' then a.uid when 1=1 then a.uid else 'null' end) as aa from dwm_asset_user_detail_comm_day a where apply_no = '20191125004334'
解决方案:
如果then的输出字段一样,则合并两个when的条件改成:
select a.apply_no, (case when coalesce(a.final_repay_date, '2999-12-31') > '2020-05-01' or coalesce(a.user_final_repay_date, '2999-12-31') > '2020-05-01' then a.uid else 'null' end) as aa from dwm_asset_user_detail_comm_day a where apply_no = '20191125004334'
如果then的输出字段不一样,则考虑从逻辑进行调整,保持各个条件的满足保持互斥关系。