A simple CASE expression has the following syntax:
case_expression ::=
CASE expressionx
WHEN expression1 [, expression2 ] ... THEN { expression | NULL }
...
[ELSE { expression | NULL }]
END
expression
Expression that returns an alphanumeric, national, numeric or time value when evaluated.
It cannot be a multiple value with a dimension greater than 1.
The values of expressionx and expression1... expressionn must have compatible data types (see section "Compatibility between data types").
expression must be contained in the THEN clause, the ELSE clause or both clauses.
The data types of the values of expression in the THEN clauses and in the ELSE clause must be compatible (see section "Compatibility between data types").
Result
The value of expressionx after CASE is compared (from left to right) with the values of the expressions expression1, expression2, .... contained in the WHEN clause. The first time a match is found, the result of the CASE expression if the contents of the associated THEN clause, i.e. the value of the associated expression or the NULL value. If the CASE expression contains several WHEN clauses, the result is the contents of the first THEN clause in whose associated WHEN clause an expression was found found which was identical to expressionx. The WHEN clauses are processed from top to bottom.
If none of the expressions (expression1... expressionn) in the WHEN clauses are identical to expressionx, the result is the contents of the ELSE clause, i.e. the value of the expression assigned to the ELSE clause or the NULL value. If you do not specify the ELSE clause, the default applies (NULL).
The data type of a simple CASE expression is derived from the data types of the values of expression that are contained in the THEN clauses and the ELSE clause. The same rules apply that apply to the data type of a CASE expression with a search condition (see "CASE expression with search condition").
A simple CASE expression corresponds to a CASE expression with a search condition of the following form:
CASE
WHEN expressionx=expression1 THEN {expression|NULL}
WHEN expressionx=expression2 THEN {expression|NULL}
...
WHEN expressionx=expressionn THEN {expression|NULL}
ELSE {expression|NULL}
END
Examples
Sort the companies in the CUSTOMERS table in accordance with their location. Here the country codes should be replaced by the names of the countries.
SELECT company,
CASE country
WHEN ' D' THEN 'Germany'
WHEN 'USA' THEN 'America'
WHEN ' CH' THEN 'Switzerland'
END
FROM customers
For payroll accounting, a distinction is to be made according to workday and weekend.
CASE EXTRACT(DAY_OF_WEEK FROM CURRENT_DATE)
WHEN 1,2,3,4,5 THEN 'workday'
WHEN 6.7 THEN 'weekend'
ELSE '?????'
END