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 input_expr, and then, in the order specified, evaluates input_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 list the full name of the division to which each employee belongs.

SELECT EMPNO, LASTNAME,
CASE WORKDEPT
WHEN 'A' THEN 'Administration'
WHEN 'B' THEN 'Human Resources'
WHEN 'C' THEN 'Accounting'
WHEN 'D' THEN 'Design'
WHEN 'E' THEN 'Operations'
END
FROM EMPLOYEE;

Equivalents in other databases

TABLE 64. Equivalents in other databases

Oracle	DECODE expression
Microsoft SQL Server	Simple CASE expression