6

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?

Question Overflow
  • 10,925
  • 18
  • 72
  • 110
  • 2
    I think that this question can help you. http://stackoverflow.com/questions/5487892/sql-server-case-when-or-then-else-end-the-or-is-not-supported – Alfaplus Jul 08 '14 at 11:31
  • 1
    cant we use IN query like WHEN IN (1, 51 ,100 , 167) – sumit Jul 08 '14 at 11:37

3 Answers3

4
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

Alfaplus
  • 1,713
  • 2
  • 19
  • 29
  • 1
    I think it would be more helpful for the OP and further visitors, if you add an explanition for your intention. – Reporter Jul 08 '14 at 12:13
  • 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. – Question Overflow Jul 08 '14 at 14:44
  • Updated. Thank you for the advice. I´m new in this world. – Alfaplus Jul 08 '14 at 15:10
  • Seems the above is for Microsoft T-SQL and not MySQL. Nevertheless, I reread the [documentation](http://dev.mysql.com/doc/refman/5.5/en/case.html) 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. – Question Overflow Jul 09 '14 at 01:45
4

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 ;
halfelf
  • 9,737
  • 13
  • 54
  • 63
Pash
  • 345
  • 4
  • 11
0

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

Frogman
  • 11
  • 3