How can I use "OR" condition in MySQL CASE expression?
case, conditional-statements, expression, mysql
Solution
CASE ranking
WHEN 1 THEN 'alpha'
WHEN 2 THEN 'beta'
WHEN 10 THEN 'charlie'
ELSE 'zelta'
END CASE;
You can use one of expresions that WHEN has, but you cannot mix both of them.
1) WHEN when_expression Is a simple expression to which input_expression is compared when the simple CASE format is used. when_expression is any valid expression. The data types of input_expression and each when_expression must be the same or must be an implicit conversion.
2) WHEN Boolean_expression Is the Boolean expression evaluated when using the searched CASE format. Boolean_expression is any valid Boolean expression.
You could program:
1)
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
2)
CASE
WHEN ListPrice = 0 THEN 'Mfg item - not for resale'
WHEN ListPrice < 50 THEN 'Under $50'
WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
ELSE 'Over $1000'
END
But in any case you can expect that the variable ranking is going to be compared in a boolean expresion.
http://msdn.microsoft.com/en-us/library/ms181765.aspx
Problem
I have a procedure that contains CASE expression statement like so: ``` BEGIN .... WHILE counter < total DO .... CASE ranking WHEN 1 OR 51 OR 100 OR 167 THEN SET project_name = 'alpha'; WHEN 2 THEN SET project_name = 'beta'; WHEN 10 OR 31 OR 40 OR 61 THEN SET project_name = 'charlie'; .... ELSE SET project_name = 'zelta'; END CASE; INSERT INTO project (id, name) VALUES (LAST_INSERT_ID(), project_name); SET counter = counter + 1; END WHILE; END $$ DELIMITER ; ``` When I call the above procedure, cases with `OR` statements are either skipped completely or only the first item in the list is matched. What am I doing wrong?