case operand type does not match when clause operand type integer vs boolean athena-CodePudding

I am trying to use case statement on an integer field in aws athena using sql and I am getting the error saying that case operand type does not match when clause operand type integer vs boolean. I have verified the field type is integer. Attaching the code below.

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;

Here is the Error

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

CodePudding user response：

There are boolean expressions following each when clauses while expression after case clause is of mismatching type.

Indeed you don't need an expression following case clause for your aim of current case, then just get rid of that such as

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