The expression after ELSE should have the same type as those after THEN: "bigint" is expected but "i

最新推荐文章于 2024-12-01 23:54:51 发布

yeweiouyang

最新推荐文章于 2024-12-01 23:54:51 发布

阅读量5k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： [问题记录]

本文链接：https://blog.youkuaiyun.com/yeweiouyang/article/details/28445559

[问题记录] 专栏收录该内容

21 篇文章

订阅专栏

本文详细介绍了在使用Hive执行SQL语句时遇到的类型不匹配错误，通过实例展示了如何将else后的常数值从int类型更改为bigint类型，从而成功解决问题。文章还提供了完整的SQL语句示例，帮助读者理解并解决类似问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

hive中执行sql语句：

select pc.category_id, 
sum(case when t.so_month between 3 and 5 then t.order_item_num else 0 end) as spring, 
sum(case when t.so_month between 6 and 8 then t.order_item_num else 0 end) as summer, 
sum(case when t.so_month between 9 and 11 then t.order_item_num else 0 end) as autumn, 
sum(case when t.so_month=12 or t.so_month<=2 then t.order_item_num else 0 end) as winnter 
from product_category pc join (select si.product_id, si.order_item_num, month(si.order_create_time) as so_month from so_item si where si.ds between '2013-05-01' and '2014-04-30' and si.is_gift=0) t on pc.product_id=t.product_id 
group by pc.category_id;

其中t.order_item_num是bigint类型

执行sql，提示错误

“The expression after ELSE should have the same type as those after THEN: "bigint" is expected but "int" is found”

解决办法：

把else后面的0改为0L即可！

select pc.category_id, 
sum(case when t.so_month between 3 and 5 then t.order_item_num else 0L end) as spring, 
sum(case when t.so_month between 6 and 8 then t.order_item_num else 0L end) as summer, 
sum(case when t.so_month between 9 and 11 then t.order_item_num else 0L end) as autumn, 
sum(case when t.so_month=12 or t.so_month<=2 then t.order_item_num else 0L end) as winnter 
from product_category pc join (select si.product_id, si.order_item_num, month(si.order_create_time) as so_month from so_item si where si.ds between '2013-05-01' and '2014-04-30' and si.is_gift=0) t on pc.product_id=t.product_id 
group by pc.category_id;