3.5.5 CASE Statement

Table of Contents Previous Next



The CASE statement executes a set of one or more statements when a specified search condition is true. The CASE statement is a stand-alone statement in itself while the previously discussed CASE expression must appear as part of an expression.
There are two formats of the CASE statement - one that is called a searched CASE and the other that uses a selector.
The selector CASE statement attempts to match an expression called the selector to the expression specified in one or more WHEN clauses. When a match is found one or more corresponding statements are executed.
CASE selector-expression
WHEN match-expression THEN
statements
[ WHEN match-expression THEN
statements
[ WHEN match-expression THEN
statements ] ...]
statements ]
selector-expression returns a value type-compatible with each match-expression. match-expression is evaluated in the order in which it appears within the CASE statement. statements are one or more SPL statements, each terminated by a semi-colon. When the value of selector-expression equals the first match-expression, the statement(s) in the corresponding THEN clause are executed and control continues following the END CASE keywords. If there are no matches, the statement(s) following ELSE are executed. If there are no matches and there is no ELSE clause, an exception is thrown.
The following example uses a selector CASE statement to assign a department name and location to a variable based upon the department number.
A searched CASE statement uses one or more Boolean expressions to determine the resulting set of statements to execute.
CASE WHEN boolean-expression THEN
statements
[ WHEN boolean-expression THEN
statements
[ WHEN boolean-expression THEN
statements ] ...]
statements ]
boolean-expression is evaluated in the order in which it appears within the CASE statement. When the first boolean-expression is encountered that evaluates to TRUE, the statement(s) in the corresponding THEN clause are executed and control continues following the END CASE keywords. If none of boolean-expression evaluates to TRUE, the statement(s) following ELSE are executed. If none of boolean-expression evaluates to TRUE and there is no ELSE clause, an exception is thrown.
The following example uses a searched CASE statement to assign a department name and location to a variable based upon the department number.


Table of Contents Previous Next