CASE

Use the CASE statement when program execution needs to take exactly one among multiple branches, depending on the value of an SPL variable or of a simple expression. The CASE statement is a logical alternative to the IF statement.

Syntax

(1)
ELSE Clause

1  ELSE  %Statement Block1
Notes:

Element Description Restrictions Syntax
constant_expr Expression that specifies a literal value Can be a literal number, quoted string, literal datetime, or literal interval. The data type must be compatible with the data type of value_expr. Constant Expressions
value_expr Expression that returns a value An SPL variable or any other type of expression that returns a value or NULL. The data type cannot be a large object (BLOB, BYTE, CLOB, TEXT), a collection, nor a user-defined OPAQUE or DISTINCT type. Among built-in OPAQUE types, only BOOLEAN and LVARCHAR are valid. Expression

Usage

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 include no WHEN clause and no ELSE clause, the CASE statement fails with a syntax error.
  • If you include no ELSE clause, but no WHEN clause specifies a constant expr that matches the value_expr, the CASE statement fails with runtime error -26062 when the routine is executed.

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

How the database server executes a CASE statement

The database server executes the CASE statement by taking the following sequence of actions:
  • The database server evaluates the value_expr expression.
  • If the resulting value matches a literal value specified as the constant_expr specification of a WHEN clause, the database server executes the statement block that immediately follows the THEN keyword in that WHEN clause.
  • If the value to which the value_expr parameter evaluates matches the constant_expr specification in more than one WHEN clause, the database server executes the statement block that immediately follows the THEN keyword in the first matching WHEN clause of the CASE statement. (In this case, the lexical order of the WHEN clauses can determine the result of the CASE statement.) If the database server executes a GOTO statement in the statement block that follows the THEN keyword, the database server transfers program control to the specified statement label. Otherwise, the database server executes the next SPL or SQL statement that follows the END CASE keywords that mark the end of the current CASE statement in the SPL routine.
  • If the value to which the value_expr parameter evaluates does not match the literal value specified in the constant_expr specification of any WHEN clause, and if the CASE statement includes an ELSE clause, the database server executes the statement block that immediately follows the ELSE keyword. If the database server executes a GOTO statement in the statement block that follows the ELSE keyword, the database server transfers program control to the specified statement label. Otherwise, the database server executes the next SPL or SQL statement that follows the END CASE keywords that mark the end of the current CASE statement in the SPL routine.
  • If the value to which the value_expr parameter evaluates does not match the literal value specified in the constant_expr specification of any WHEN clause, and if the CASE statement does not includes an ELSE clause, the database server issues an exception, and the CASE statement fails with error -26062. Whether the SPL routine terminates or continues to execute depends on its exception-handling logic.

This implementation of the CASE statement closely resembles that of HCL OneDB™ Parallel Server, except that HCL OneDB Parallel Server issues no error when no ELSE clause is specified and no WHEN clause matches the value_expr parameter. In this case, program execution continues at the SPL or SQL statement that immediately follows the CASE statement.

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

Evaluating the value expression in a CASE statement

The database server calculates the value of the value_expr parameter only once, at the start of execution of the CASE statement. If the expression specified in that parameter contains one or more SPL variables, and the value of any of these variables subsequently changes in one of the statement blocks within the CASE statement, the database server does not recalculate the value of the value_expr parameter. For this reason, any change in the value of variables specified in the value_expr parameter has no effect on the branch taken by the CASE statement.

Examples of CASE statements

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; --invalid value
END CASE;

Here each WHEN clause specifies a literal integer as its constant expression, under the assumption that the value expression has a numeric data type. (If these literal values had been delimited by quotation marks, the database server would treat them as character values.)

The following example includes a test for NULL in a WHEN clause, where the value expression and constant expression data types are CHAR(1): 

CREATE PROCEDURE case_proc( ) 
RETURNING CHAR(1);
DEFINE grade CHAR(1);
LET grade = 'D';
CASE grade
   WHEN 'A' THEN LET grade = 'a';
   WHEN 'B' THEN LET grade = 'b';
   WHEN 'C' THEN LET grade = 'c';  
   WHEN NULL THEN LET grade = 'z';
   ELSE LET grade = 'd';
END CASE;
RETURN grade;
END PROCEDURE;

Related statement

IF