How do I use properly CASE..WHEN in MySQL

Question

Here is a demo query, notice it is very simple, Fetches only where base_price is 0, And still, it chooses the condition 3:

SELECT
   CASE course_enrollment_settings.base_price
    WHEN course_enrollment_settings.base_price = 0      THEN 1
    WHEN course_enrollment_settings.base_price<101      THEN 2
    WHEN course_enrollment_settings.base_price>100 AND   
                      course_enrollment_settings.base_price<201 THEN 3
        ELSE 6
   END AS 'calc_base_price',
   course_enrollment_settings.base_price
FROM
    course_enrollment_settings
WHERE course_enrollment_settings.base_price = 0

base_price is decimal(8,0)

When run this on my DB, I get:

3 0
3 0
3 0
3 0
3 0

score 80 · Accepted Answer · edited Feb 25 '16 at 11:31

80

Remove the course_enrollment_settings.base_price immediately after CASE:

SELECT
   CASE
    WHEN course_enrollment_settings.base_price = 0      THEN 1
    ...
    END

CASE has two different forms, as detailed in the manual. Here, you want the second form since you're using search conditions.

edited Feb 25 '16 at 11:31

Aritz

30,971
16
136
217

answered Mar 06 '12 at 16:45

NPE

486,780
108
951
1,012

If you wanted to sort by the values, what would the order by clause look like since there is not a column alias? – HPWD Aug 23 '17 at 21:14

kapil das · Answer 2 · 2013-07-17T06:16:25.353

46

CASE case_value
    WHEN when_value THEN statements
    [WHEN when_value THEN statements]
    ELSE statements
END

Or:

CASE
WHEN <search_condition> THEN statements
[WHEN <search_condition> THEN statements] 
ELSE statements
END

here CASE is an expression in 2nd scenario search_condition will evaluate and if no search_condition is equal then execute else

SELECT
   CASE course_enrollment_settings.base_price
    WHEN course_enrollment_settings.base_price = 0      THEN 1

should be

SELECT
   CASE 
    WHEN course_enrollment_settings.base_price = 0      THEN 1

edited Jul 17 '13 at 06:16

answered Jul 17 '13 at 05:47

kapil das

2,061
1
28
29

3

what are the brackets ? `[` and `]` – Shafizadeh Jul 20 '15 at 00:41
1

@Shafizadeh those imply that you could add more when statements after the first. – Douwe de Haan Jan 24 '20 at 10:22

a1ex07 · Answer 3 · 2012-03-06T16:54:35.283

17

CASE course_enrollment_settings.base_price is wrong here, it should be just CASE

SELECT 
CASE 
WHEN course_enrollment_settings.base_price = 0      THEN 1 
WHEN course_enrollment_settings.base_price<101      THEN 2 
WHEN course_enrollment_settings.base_price>100 AND    
                  course_enrollment_settings.base_price<201 THEN 3 
    ELSE 6 
END AS 'calc_base_price', 
course_enrollment_settings.base_price 
FROM 
  course_enrollment_settings 
WHERE course_enrollment_settings.base_price = 0

Some explanations. Your original query will be executed as :

SELECT 
CASE 0
WHEN 0=0 THEN 1 -- condition evaluates to 1, then 0 (from CASE 0)compares to 1 - false
WHEN 0<1 THEN 2 -- condition evaluates to 1,then 0 (from CASE 0)compares to 1 - false
WHEN 0>100 and 0<201 THEN 3 -- evaluates to 0 ,then 0 (from CASE 0)compares to 0 - true
ELSE 6, ...

it's why you always get 3

edited Mar 06 '12 at 16:54

answered Mar 06 '12 at 16:45

a1ex07

36,826
12
90
103

1

"No need for" is imprecise. It's "you _should not_ have". – Lightness Races in Orbit Mar 06 '12 at 16:50
@Lightness Races in Orbi: agree; I changed it and added some explanations – a1ex07 Mar 06 '12 at 16:57
I was getting an error when I added a column alias `AS 'calc_base_price`. I solved it by adding parenthesis around the whole `(CASE ... END)` – pgr Jul 17 '18 at 11:39

score 7 · Answer 4 · answered Mar 06 '12 at 16:46

SELECT
   CASE 
    WHEN course_enrollment_settings.base_price = 0      THEN 1
    WHEN course_enrollment_settings.base_price>0 AND  
         course_enrollment_settings.base_price<=100     THEN 2
    WHEN course_enrollment_settings.base_price>100 AND   
         course_enrollment_settings.base_price<201      THEN 3
        ELSE 6
   END AS 'calc_base_price',
   course_enrollment_settings.base_price
FROM
    course_enrollment_settings
WHERE course_enrollment_settings.base_price = 0

Changed ur conditions a little.. Added <=100 instead of <101 — Teja, Mar 06 '12 at 16:47

score 6 · Answer 5 · answered Mar 06 '12 at 16:50

There are two variants of CASE, and you're not using the one that you think you are.

What you're doing

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

Each condition is loosely equivalent to a if (case_value == when_value) (pseudo-code).

However, you've put an entire condition as when_value, leading to something like:

if (case_value == (case_value > 100))

Now, (case_value > 100) evaluates to FALSE, and is the only one of your conditions to do so. So, now you have:

if (case_value == FALSE)

FALSE converts to 0 and, through the resulting full expression if (case_value == 0) you can now see why the third condition fires.

What you're supposed to do

Drop the first course_enrollment_settings so that there's no case_value, causing MySQL to know that you intend to use the second variant of CASE:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

Now you can provide your full conditionals as search_condition.

Also, please read the documentation for features that you use.

What I needed -- this was clear enough that I could actually adapt it to my situation. — Betty Mock, Oct 25 '15 at 21:38

score 1 · Answer 6 · answered Mar 06 '12 at 16:51

I think part of it is that you're stating the value you're selecting after CASE, and then using WHEN x = y syntax afterward, which is a combination of two different methods of using CASE. It should either be

CASE X
  WHEN a THEN ...
  WHEN b THEN ...

or

CASE
  WHEN x = a THEN ...
  WHEN x = b THEN ...

How do I use properly CASE..WHEN in MySQL

6 Answers6

What you're doing

What you're supposed to do

Linked

Related