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?
asked Jul 8, 2014 at 11:26
Question OverflowQuestion Overflow
10.5k18 gold badges71 silver badges108 bronze badges
2
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.
//msdn.microsoft.com/en-us/library/ms181765.aspx
answered Jul 8, 2014 at 11:34
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,21713 gold badges51 silver badges62 bronze badges
answered Dec 11, 2018 at 6:18
PashPash
1973 silver badges10 bronze badges
This is also possible: select [case when [var1 = 0 or var2 = 1] then 'x' else 'y' end] from...
answered Oct 8, 2021 at 9:05
One is matching value and other one is matching condition.
First Type [ matching value ]
MySQL Query Comparing with value, Syntax is hereCASE A_value
WHEN B_value THEN B_statement
[WHEN C_Value THEN C_statement] ...
[ELSE X_statement]
END CASE
Video on SQL CASE WHEN .. THEN
Here A_value is matched with different values given under WHEN. If A_value equals to B_value then B_statement is executed. If it is not matched than next WHEN value C_value is checked. If nothing is matched then statement under X_statement is executed.
If any WHEN statement
is matched then rest WHEN statements are not checked and matched statement is returned. Let us try one example
We will use our student table for this example. We have allotted rooms for the classes at different floors in a school. Our list should display student details with class and location of the class. Using CASE in our query statement is here.
SELECT `id` , `name` , `class` , `mark` , `gender` ,
CASE class
WHEN 'four' THEN '1st floor'
WHEN 'five' THEN '2nd floor'
WHEN 'three' THEN '2nd floor'
WHEN 'two' THEN '1st floor'
ELSE 'Ground floor'
END AS location
FROM `student`
id | name | class | mark | gender | location |
1 | John Deo | Four | 75 | male | 1st floor |
2 | Max Ruin | Three | 85 | male | 2nd floor |
3 | Arnold | Three | 55 | male | 2nd floor |
4 | Krish Star | Four | 60 | male | 1st floor |
5 | John Mike | Four | 60 | male | 1st floor |
6 | Alex John | Four | 55 | male | 1st floor |
7 | My John Rob | Fifth | 78 | male | Ground floor |
8 | Asruid | Five | 85 | male | 2nd floor |
9 | Tes Qry | Six | 78 | male | Ground floor |
10 | Big John | Four | 55 | male | 1st floor |
We can display based on the floor wise or location wise by using order by query
SELECT `id` , `name` , `class` , `mark` , `gender` ,
CASE class
WHEN 'four' THEN '1st floor'
WHEN 'five' THEN '2nd floor'
WHEN 'three' THEN '2nd floor'
WHEN 'two' THEN '1st floor'
ELSE 'Ground floor'
END AS location
FROM `student` ORDER BY location
Second Type [Matching condition with CASE statement]
In this case we don't specify any value or data to be matched, instead we work on testing some conditions and if it is True then respective statement is to be executed.CASE
WHEN condition_to_check THEN statement
[WHEN condition_to_check THEN statement ...
[ELSE statement]
END CASE
Here we
check the condition and if it is True then the respective statement is executed. Example :
We will try our student example. Here student grade is awarded based on the mark they got. Here is the query
SELECT `id` , `name` , `class` , `mark` , `gender` ,
CASE
WHEN mark >= 90 THEN 'A'
WHEN mark >= 80 THEN 'B'
WHEN mark >= 70 THEN 'C'
ELSE 'FAIL'
END AS grade
FROM `student`
id | name | class | mark | gender | grade |
1 | John Deo | Four | 75 | male | C |
2 | Max Ruin | Three | 85 | male | B |
3 | Arnold | Three | 55 | male | FAIL |
4 | Krish Star | Four | 60 | male | FAIL |
5 | John Mike | Four | 60 | male | FAIL |
6 | Alex John | Four | 55 | male | FAIL |
7 | My John Rob | Fifth | 78 | male | C |
8 | Asruid | Five | 85 | male | B |
9 | Tes Qry | Six | 78 | male | C |
10 | Big John | Four | 55 | male | FAIL |
11 | Ronald | Six | 89 | male | B |
12 | Recky | Six | 94 | male | A |
13 | Kty | Seven | 88 | male | B |
Using BETWEEN Query
While grouping in a rang it is better to use MySQL BETWEEN Query
SELECT `id` , `name` , `class` , `mark` , `gender` ,
CASE
WHEN mark BETWEEN 90 AND 100 THEN 'A'
WHEN mark BETWEEN 80 AND 89 THEN 'B'
WHEN mark BETWEEN 70 AND 79 THEN 'C'
ELSE 'FAIL'
END AS grade
FROM `student`
CASE with GROUP BY
We can further break the gender column to get number of male and female in each class by using SQL GROUP BY.SELECT class, count[*] as Total,
sum[CASE WHEN gender ='male' THEN 1 ELSE 0 END] as Male,
sum[CASE WHEN gender ='Female' THEN 1 ELSE 0 END] as Female
FROM student group by class;
Output
Eight | 1 | 1 | 0 |
Five | 3 | 3 | 0 |
Four | 9 | 4 | 5 |
Nine | 2 | 1 | 1 |
Seven | 10 | 5 | 5 |
Six | 7 | 2 | 5 |
Three | 3 | 2 | 1 |
Using with NULL data
If any column has NULL data and we want to return 'checked' when it is not null and return 'not_checked' when it is NULL.SELECT CASE WHEN c_name IS NOT NULL THEN 'checked'
ELSE 'not_checked'
END as my_status from STUDENT
Here c_name is the column name storing the status of the record, checked or not. Download the SQL dump of the above student table
Example
By using SQL CASE we can get records based on Financial Year wise.IF[] function SQL sum in multiple columns GROUP BY Query