Simple CASE Expression

Syntax

CASE expr
WHEN when_expr THEN result_expr
{WHEN when_expr THEN result_expr}
[ELSE else_expr]
END

Simple CASE evaluates expr, and then, in the order specified, evaluates expr = when_expr for each WHEN clause. Simple CASE returns the result_expr of the first WHEN clause that evaluates to TRUE. If no match is found, CASE returns the else_expr if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

Example

This example uses a CASE expression to alter the display of book categories to make them more understandable.

SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END,
price AS Price
FROM titles
Equivalents in other databases

TABLE 78. Equivalents in other databases

IBM DB2	Simple CASE expression
Oracle	DECODE expression