MYSQL case statement not working with simple statement

Question

I really baffled on why this simple case statement will not work.

SELECT avg_weight,
    CASE avg_weight
        WHEN avg_weight BETWEEN 0 AND 2000 THEN 'data'
    ELSE 'No Data'
END AS wt_type 
FROM tbl_prices;

results are like this:

avg_weight     wt_type
1050           No data
833            No data
990            No data

The column is avg_weight, smallinit(4)

If I change the value of 1050 to 0, then I get data in the wt_type column

I have also tried

WHEN avg_weight > 0 AND avg_weight < 2000 THEN 'data'

but I get the same results.

Possible duplicate of [Using CASE, WHEN, THEN, END in a select query with MySQL](https://stackoverflow.com/questions/5656440/using-case-when-then-end-in-a-select-query-with-mysql) — cdaiga, Feb 12 '18 at 17:59

score 2 · Accepted Answer · answered Feb 12 '18 at 17:59

Change your CASE statement to:

CASE 
    WHEN avg_weight BETWEEN 0 AND 2000 THEN 'data'
ELSE 'No Data'

Case statements take two forms:

Form 1:

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

Form 2:

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

Because in your original case statement you specified a CASE value as in the first form, so you were comparing (for you first record) the value 1050 to the when_value of true and 1050 <> true so it failed.

Instead you want Form 2 wherein you specify a search_condition only. Please refer to the manual for more info.

Here is a sqlfiddle showing this in action

MYSQL case statement not working with simple statement

1 Answers1