Case statement

Posted on Edited on In

 

There are two types Case statement:

1.Case statement 1

syntax: 

1
2
3
4
5
6
CASE [field]
    WHEN [value_1] THEN [value_a]
    WHEN [value_2] THEN [value_b]
    WHEN [value_3] THEN [value_c]
    ELSE [value_default]
END

example: 

1
2
3
4
5
6
7
8
9
10
SELECT commodity_name,
    (
        CASE standard_code
            WHEN 'code1' THEN 'standard 1'
            WHEN 'code2' THEN 'standard 2'
            WHEN 'code3' THEN 'standard 3'
            ELSE 'other'
        END
    ) as standard_name
from commodity;

2.Case statement 2

syntax: 

1
2
3
4
5
6
CASE
    WHEN [condition_1] THEN [value_1]
    WHEN [condition_2] THEN [value_2]
    WHEN [condition_3] THEN [value_3]
    ELSE [value_default]
END

example 1: 

1
2
3
4
5
6
7
8
9
10
SELECT commodity_name,
    (
        CASE
            WHEN standard_code = 'code1' THEN 'standard 1'
            WHEN standard_code = 'code2' THEN 'standard 2'
            WHEN standard_code = 'code3' THEN 'standard 3'
            ELSE 'other'
        END
    ) as standard_name
from commodity;

example 2: 

1
2
3
4
5
6
7
8
9
SELECT team_name,
(
	CASE
		WHEN score < 60 THEN 'low'
		WHEN score between 60 and 80 THEN 'medium'
		WHEN score > 80 THEN 'high'
	END
) as result
from team_record;