3.5.4 CASE Expression

Table of Contents Previous Next



The CASE expression returns a value that is substituted where the CASE expression is located within an expression.
There are two formats of the CASE expression - one that is called a searched CASE and the other that uses a selector.
The selector CASE expression attempts to match an expression called the selector to the expression specified in one or more WHEN clauses. result is an expression that is type-compatible in the context where the CASE expression is used. If a match is found, the value given in the corresponding THEN clause is returned by the CASE expression. If there are no matches, the value following ELSE is returned. If ELSE is omitted, the CASE expression returns null.
CASE selector-expression
WHEN match-expression THEN
result
[ WHEN match-expression THEN
result
[ WHEN match-expression THEN
result ] ...]
[ ELSE
result ]
match-expression is evaluated in the order in which it appears within the CASE expression. result is an expression that is type-compatible in the context where the CASE expression is used. When the first match-expression is encountered that equals selector-expression, result in the corresponding THEN clause is returned as the value of the CASE expression. If none of match-expression equals selector-expression then result following ELSE is returned. If no ELSE is specified, the CASE expression returns null.
The following example uses a selector CASE expression to assign the department name to a variable based upon the department number.
A searched CASE expression uses one or more Boolean expressions to determine the resulting value to return.
CASE WHEN boolean-expression THEN
result
[ WHEN boolean-expression THEN
result
[ WHEN boolean-expression THEN
result ] ...]
[ ELSE
result ]
boolean-expression is evaluated in the order in which it appears within the CASE expression. result is an expression that is type-compatible in the context where the CASE expression is used. When the first boolean-expression is encountered that evaluates to true, result in the corresponding THEN clause is returned as the value of the CASE expression. If none of boolean-expression evaluates to true then result following ELSE is returned. If no ELSE is specified, the CASE expression returns null.
The following example uses a searched CASE expression to assign the department name to a variable based upon the department number.


Table of Contents Previous Next