DevEx DevEx - 1 month ago 7
SQL Question

SQL combine CASE and AND

How to combine an SQL

CASE
statement with multiple
AND
, such that I can check for multiple conditions i.e.
I want to randomly change
region
from
233
to
244
for
shopid = 455
:

select
id,
case region
when 233 and shopid = 455 and FLOOR(RAND()*(3-1)+1) = 1 then 244
when 233 and shopid = 455 and FLOOR(RAND()*(3-1)+1) = 2 then 233
else region
end
from
table1


I get this error:


Operand '233' part of predicate '233 AND shopid = 455' should return type 'BOOLEAN' but returns type 'INT'.


FYI
FLOOR(RAND()*(3-1)+1)
will either yield 1 or 2

Answer

Use the other form of case:

select
id,
case 
  when region = 233 and shopid = 455 and FLOOR(RAND()*(3-1)+1) = 1 then 244
  when region = 233 and shopid = 455 and FLOOR(RAND()*(3-1)+1) = 2 then 233
  else region
end
from table1
Comments