>>-CASE--value_expr--------------------------------------------->
 
                               (1)
>--+-ELSE--| Statement Block |-------------------------------------------------------------------+-->
   | .-----------------------------------------------------.                                     |
   | V                                                (1)  |                                     |
   '---WHEN--constant_expr--THEN--| Statement Block |------+--+--------------------------------+-'
                                                              |                           (1)  |
                                                              '-ELSE--| Statement Block |------'
 
>--END CASE----------------------------------------------------><
 

1. See Statement Block

You can use the CASE statement to create a set of conditional branches within an SPL routine. The WHEN and the ELSE clauses are optional, but you must include at least one of them. If you specify no WHEN and no ELSE clause, you receive a syntax error.

3Do not confuse the CASE statement with CASE expressions 3of SQL. (For Extended Parallel Server, CASE expressions support the same keywords 3as the CASE statement, but use different syntax and semantics to evaluate conditions that you specify and return a single value 3or NULL, as described in CASE Expressions.)

The database server executes the CASE statement in the following way:

• The database server evaluates the value_expr parameter.
• If the resulting value matches a literal value specified in the constant_expr parameter of a WHEN clause, the database server executes the statement block that follows the THEN keyword in that WHEN clause.
• If the value resulting from the evaluation of the value_expr parameter matches the constant_expr parameter in more than one WHEN clause, the database server executes the statement block that follows the THEN keyword in the first matching WHEN clause in the CASE statement.
• After the database server executes the statement block that follows the THEN keyword, it executes the statement that follows the CASE statement in the SPL routine.
• If the value of the value_expr parameter does not match the literal value specified in the constant_expr parameter of any WHEN clause, and if the CASE statement includes an ELSE clause, the database server executes the statement block that follows the ELSE keyword.
• If the value of the value_expr parameter does not match the literal value specified in the constant_expr parameter of any WHEN clause, and if the CASE statement does not include an ELSE clause, the database server executes the statement that follows the CASE statement in the SPL routine.
• If the CASE statement includes an ELSE clause but not a WHEN clause, the database server executes the statement block that follows the ELSE keyword.

The statement block that follows the THEN or ELSE keywords can include any SQL statement or SPL statement that is valid in the statement block of an SPL routine. For more information, see Statement Block.

The database server computes the value of the value_expr parameter only one time. It computes this value at the start of execution of the CASE statement. If the value expression specified in the value_expr parameter contains SPL variables and the values of these variables change subsequently in one of the statement blocks within the CASE statement, the database server does not recompute the value of the value_expr parameter. So a change in the value of any variables contained in the value_expr parameter has no effect on the branch taken by the CASE statement.

In the following example, the CASE statement initializes one of a set of SPL variables (named j, k, l, and m) to the value of an SPL variable named x, depending on the value of another SPL variable named i:

CASE i
   WHEN 1 THEN LET j = x;
   WHEN 2 THEN LET k = x;
   WHEN 3 THEN LET l = x;
   WHEN 4 THEN LET m = x;
   ELSE
      RAISE EXCEPTION 100; --illegal value
END CASE

IF