mysqlswitch-statementcoding-stylecase-statementcode-readability

Is there any way for grouping cases in MySQL CASE statements?


Is there any short form for writing MySQL CASE statements as in PHP switch? For example, if i have:

SELECT
    CASE Type
        WHEN 1 THEN "A"
        WHEN 5 THEN "B"
        WHEN 10 THEN "B"
        WHEN 20 THEN "B"
        WHEN 30 THEN "B"
        WHEN 300 THEN "C"
        ELSE "-"
    END TypeDesc

In PHP syntax we can write it in brief:

switch ($Type) {
    case 1: $TypeDesc = 'A'; break; 
    case 5:
    case 10:
    case 20:
    case 30: $TypeDesc = 'B'; break; 
    case 300: $TypeDesc = 'C'; break;
    default: $TypeDesc = '-'; }

I tried:

WHEN 5 OR 10 OR 20 OR 30 THEN "B"

and

WHEN IN(5, 10, 20, 30) THEN "B"

and these two:

WHEN 5
WHEN 10
WHEN 20
WHEN 30 THEN "B"

WHEN 5 THEN
WHEN 10 THEN
WHEN 20 THEN
WHEN 30 THEN "B"

But i get incorrect result or error!


Solution

  • Use

    case when <condition>
    

    instead of

    case <variable> when <value>
    

    like this

    SELECT
        CASE WHEN Type = 1 THEN "A"
             WHEN Type < 300 THEN "B"
             WHEN Type = 300 THEN "C"
             ELSE "-"
        END TypeDesc