SQL Case statement conditionals

Question

So im trying to use a case statement to update a column in a table with a lot of specific conditions. the code I have below should be evident what my goal is, but I am getting an error saying:

ERROR: Operand of WHEN clause 1 is not the same data type as the CASE operand.
ERROR: Operand of WHEN clause 3 is not the same data type as the CASE operand.
ERROR: Operand of WHEN clause 4 is not the same data type as the CASE operand.
ERROR: Operand of WHEN clause 5 is not the same data type as the CASE operand.
ERROR: Operand of WHEN clause 6 is not the same data type as the CASE operand.

I believe this is because the or statements are logicals and not strings as they should be on the right side? code is below, any help is appreciated.

update cat_rotn_data
set rotn_ac_typ_cd = case rotn_ac_typ_cd
    when '31J' or '320' or '321' or '319' or'32K' or'32M' or'32R' or'3HF' or'3KR' or'3MR' then '320' 
    when '717' then '717' 
    when 'M88' or 'M90' or 'M9K' then 'M88'
    when '738' or'739' or '73A' or '73E' or '73W' or '73H' or '700' then '73N'
    when '332' or '333' or '33X' or '339' or '330' then '330' 
    when '757' or '75C' or '75D' or '75G' or '75H' or '75P' or '75S' or '75Y' or '76L' or '76P' or '76Q' or '76T' or '76Z' or '75N' or'7ER' then '7ER'
    when '777' or '77B' or '7HD' or '7HB' or  '7CD' or '7CB' then '777'

    else 'ERROR'
    end;

Does this answer your question? [SELECT using 'CASE' in SQL](https://stackoverflow.com/questions/21313025/select-using-case-in-sql) — Sam M, May 24 '21 at 15:08
Wait, `rotn_ad_typ_cd := '73N'` becomes `'ERROR'` instead of `'73N'`? (Or was there a typo around `'73H'` and `'73N'`?) — pilcrow, May 24 '21 at 15:32

score 1 · Accepted Answer · answered May 24 '21 at 15:04

You need a boolean expression for each when condition:

set rotn_ac_typ_cd = case 
     (case when rotn_ac_typ_cd in ('31J', '320', '321', '319', '32K', '32M', '32R', '3HF', '3KR', '3MR')
           then '320' 
           when rotn_ac_typ_cd in ('717')
           then '717' 
           . . .
           else 'ERROR'
      end);

or -- in SQL -- connects boolean expressions and you are using it to connect strings, which is causing your error.

score 0 · Answer 2 · edited May 24 '21 at 18:06

You can use IN rather than using multiple ORs in the statement. In the update statement you are expecting 'M88' when the rotn_ac_typ_cd values are 'M90' or 'M9K' or 'M88'. You can only give one check for one WHEN statement. If you still want to use OR then you can do the below

update cat_rotn_data
set rotn_ac_typ_cd = case WHEN rotn_ac_typ_cd= 'M88' THEN 'M88'
                          WHEN rotn_ac_typ_cd= 'M0' THEN 'M88'
                          WHEN rotn_ac_typ_cd= 'M9K' THEN 'M88' 
                     else ... end

But this will make your query bigger, you can simply use IN.

update cat_rotn_dat
set rotn_ac_typ_cd = case WHEN rotn_ac_typ_cd IN ('M88', 'M90', 'M9K') THEN 'M88' 
    else ... end

SQL Case statement conditionals

2 Answers2