CASE statement

The CASE statement selects an execution path based on the evaluation of one or more conditions. A CASE statement operates in the same way as a CASE expression.

Syntax

>>-CASE--+-simple-when-clause---+------------------------------->
         '-searched-when-clause-'   

>--+--------------------------------------+--END CASE----------><
   |       .----------------------------. |             
   |       V                            | |             
   '-ELSE----SQL-procedure-statement--;-+-'


simple-when-clause:

>>-expression--------------------------------------------------->

   .--------------------------------------------------------.   
   |                         .----------------------------. |   
   V                         V                            | |   
>----WHEN--expression--THEN----SQL-procedure-statement--;-+-+--><

searched-when-clause:

   .--------------------------------------------------------------.   
   |                               .----------------------------. |   
   V                               V                            | |   
>>---WHEN--search-condition--THEN----SQL-procedure-statement--;-+-+-><

Description

CASE: Begins a case-expression.
simple-when-clause: Specifies the expression prior to the first WHEN keyword that is tested for equality with the value of each expression that follows the WHEN keyword, and the result to be executed when those expressions are equal. If the comparison is true, the THEN statement is executed. If the result is unknown or false, processing continues to the next expression or the ELSE statement.
The data type of the expression prior to the first WHEN keyword must be comparable to the data types of each expression that follows the WHEN keywords.
searched-when-clause: Specifies the search-condition that is applied to each row or group of table data presented for evaluation, and the result when that condition is true. search-condition cannot contain a fullselect. If the search condition is true, the THEN statement is executed. If the condition is unknown or false, processing continues to the next search condition or the ELSE statement.
SQL-procedure-statement: Specifies a statement that follows the THEN and ELSE keyword. The statement specifies the result of a searched-when-clause or a simple-when-clause that is true, or the result if no case is true. The statement must be one of the statements listed under SQL-procedure-statement.
search-condition: Specifies a condition that is true, false, or unknown about a row or group of table data.
ELSE SQL-procedure-statement: If none of the conditions specified in the simple-when-clause or searched-when-clause are true, the statements in the else-clause are executed.
If none of the conditions specified in the WHEN clause are true and an ELSE clause is not specified, an error is returned at run time, and the execution of the CASE statement is terminated.
END CASE: Ends a case-statement.

Notes

If none of the conditions specified in the WHEN clause are true and an ELSE clause is not specified, an error is returned at run time, and the execution of the CASE statement is terminated.

CASE statements that use a simple case statement WHEN clause can be nested up to three levels. CASE statements that use a searched statement WHEN clause have no limit to the number of nesting levels.

Considerations for the SQLSTATE and SQLCODE SQL variables: When the first SQL-procedure-statement in the CASE statement is executed, the SQLSTATE and SQLCODE SQL variables reflect the result of evaluating the expression or search conditions of that CASE statement. If a CASE statement does not include an ELSE clause and none of the search conditions evaluate to true, an error is returned.

Examples

Example 1: Use a simple case statement WHEN clause to update column DEPTNAME in table DEPT, depending on the value of SQL variable v_workdept.

CASE v_workdept
 WHEN 'A00'
  THEN UPDATE DEPT SET
   DEPTNAME = 'DATA ACCESS 1';
 WHEN 'B01'
  THEN UPDATE DEPT SET
   DEPTNAME = 'DATA ACCESS 2';
 ELSE UPDATE DEPT SET
   DEPTNAME = 'DATA ACCESS 3';
END CASE

Example 2: Use a searched case statement WHEN clause to update column DEPTNAME in table DEPT, depending on the value of SQL variable v_workdept.

CASE
 WHEN v_workdept < 'B01'
  THEN UPDATE DEPT SET
   DEPTNAME = 'DATA ACCESS 1';
 WHEN v_workdept < 'C01'
  THEN UPDATE DEPT SET
   DEPTNAME = 'DATA ACCESS 2';
 ELSE UPDATE DEPT SET
   DEPTNAME = 'DATA ACCESS 3';
END CASE