How can I use "OR" condition in MySQL CASE expression?

Question

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?

Hierocracy

I think that this question can help you. #5488392 – Cephalochordate

cant we use IN query like WHEN IN (1, 51 ,100 , 167) – Level

Answer 1

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

Cephalochordate

I think it would be more helpful for the OP and further visitors, if you add an explanition for your intention. – Famulus

Thanks for the useful link above. As pointed out by reporter, the current answer does not explain anything nor is it coded properly. Try to improve on it if you want to earn some reputation points. – Hierocracy

Updated. Thank you for the advice. I´m new in this world. – Cephalochordate

Seems the above is for Microsoft T-SQL and not MySQL. Nevertheless, I reread the documentation and noted that they work the same. The main takeaway is that the first format accepts a value only while the second format accepts a search condition which may include a boolean "OR" condition. – Hierocracy

Answer 2

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 ; 

Ignatz

Answer 3

This is also possible: select (case when (var1 = 0 or var2 = 1) then 'x' else 'y' end) from...

Wound