子句操作数类型integer与布尔值athena时，大小写操作数类型不匹配

Question

我试图使用sql在aws athena中的整数字段上使用case语句，但我收到错误消息，当子句操作数类型integer与boolean时，case操作数类型不匹配。我已验证字段类型为integer。附上下面的代码。

SELECT a.open_dt, 
Case a.terms_duration when a.terms_duration<=6 then '<=6'
when a.terms_duration>6 and a.terms_duration<=12 then '6-12'
when a.terms_duration>12 and a.terms_duration<=18 then '12-18'
when a.terms_duration>18 and a.terms_duration<=36 then '18-36'
when a.terms_duration>36 and a.terms_duration<=48 then '36-48'
when a.terms_duration>48 and a.terms_duration<=60 then '48-60'
when a.terms_duration>60 then '60+'
Else 'Missing' END
FROM sx06grp.sx06am_pmnt_sbfe_crdb_sampleno_1_hp a limit 10;

这是错误

An error occurred when executing the SQL command:
SELECT a.open_dt, 
Case a.terms_duration when a.terms_duration<=6 then '<=6'
when a.terms_duration>6 and a.terms_duration<=12 then '6-12'
when...
[Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client. SYNTAX_ERROR: line 2:23: CASE operand type does not match WHEN clause operand type: integer vs boolean [SQL State=HY000, DB Errorcode=100071]
1 statement failed.
Execution time: 0.6s

Answer 1

每个when子句后面都有布尔表达式，而case子句后面的表达式类型不匹配。

事实上，对于当前案例，您不需要case子句后面的表达式，然后就可以去掉之类的表达式

CASE 
WHEN a.terms_duration <= 6 THEN
'<=6'
WHEN a.terms_duration >  6 AND a.terms_duration <= 12 THEN
'6-12'
WHEN a.terms_duration > 12 AND a.terms_duration <= 18 THEN
'12-18'
WHEN a.terms_duration > 18 AND a.terms_duration <= 36 THEN
'18-36'
WHEN a.terms_duration > 36 AND a.terms_duration <= 48 THEN
'36-48'
WHEN a.terms_duration > 48 AND a.terms_duration <= 60 THEN
'48-60'
WHEN a.terms_duration > 60 THEN
'60+'
ELSE
'Missing'
END