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?
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
you can use in to compare the values both numeric or character
CASE
WHEN ranking in(1,2,3) THEN '1Q'
WHEN ranking in(4,5,6) THEN '2Q'
ELSE '3Q'
END CASE;
CASE
WHEN ranking in('1','2','3') THEN '1Q'
WHEN ranking in('4','5','6') THEN '2Q'
ELSE '3Q'
END CASE;
this will also work in select statement and stored procedure also.
select case when month(curdate()) in (4,5,6) then 1 when month(curdate()) in (7,8,9) then 2 else 3 end as fiscal_quarter ;