The CASE expression uses the SQL statement to perform an IF ... THEN statement. When a result of comparison expression specified in a WHEN clause is true, a value specified in THEN value is returned. A value specified in an ELSE clause is returned otherwise. If no ELSE clause exists, NULL is returned.
CASE control_expression simple_when_list
[ else_clause ]
END
CASE searched_when_list
[ else_clause ]
END
simple_when :
WHEN expression THEN result
searched_when :
WHEN search_condition THEN result
else_clause :
ELSE result
result :
expression | NULL
The CASE expression must end with the END keyword. A control_expression argument and an expression argument in simple_when expression should be comparable data types. The data types of result specified in the THEN ... ELSE statement should all same, or they can be convertible to common data type.
The data type for a value returned by the CASE expression is determined based on the following rules.
--creating a table
CREATE TABLE case_tbl( a INT);
INSERT INTO case_tbl VALUES (1);
INSERT INTO case_tbl VALUES (2);
INSERT INTO case_tbl VALUES (3);
INSERT INTO case_tbl VALUES (NULL);
--case operation with a search when clause
SELECT a,
CASE WHEN a=1 THEN 'one'
WHEN a=2 THEN 'two'
ELSE 'other'
END
FROM case_tbl;
a case when a=1 then 'one' when a=2 then 'two' else 'other' end
===================================
1 'one'
2 'two'
3 'other'
NULL 'other'
--case operation with a simple when clause
SELECT a,
CASE a WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'other'
END
FROM case_tbl;
a case a when 1 then 'one' when 2 then 'two' else 'other' end
===================================
1 'one'
2 'two'
3 'other'
NULL 'other'
--result types are converted to a single type containing all of significant figures
SELECT a,
CASE WHEN a=1 THEN 1
WHEN a=2 THEN 1.2345
ELSE 1.234567890
END
FROM case_tbl;
a case when a=1 then 1 when a=2 then 1.2345 else 1.234567890 end
===================================
1 1.000000000
2 1.234500000
3 1.234567890
NULL 1.234567890
--an error occurs when result types are not convertible
SELECT a,
CASE WHEN a=1 THEN 'one'
WHEN a=2 THEN 'two'
ELSE 1.2345
END
FROM case_tbl;
ERROR: Cannot coerce 'one' to type double.