CODEWITHSUNDEEP
mysql
cstewart28
cstewart28

Reputation: 15

MYSQL case statement not working with simple statement

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.

Upvotes: 0

Views: 1693

Answers (1)

JNevill
JNevill

Reputation: 50034

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

Upvotes: 2

Related Questions