CASE statement

The CASE statement selects an execution path based on multiple conditions. A CASE statement operates in the same way as a CASE expression.

Syntax


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

>--+-------------+--END CASE-----------------------------------><
   '-else-clause-'


simple-when-clause:

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

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

searched-when-clause:

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

else-clause:

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

Description

label: Specifies the label for the CASE statement. The label name cannot be the same as the name of the SQL routine or another label within the same scope. For additional information, see References to labels.
CASE: Begins a case-expression.
simple-when-clause: The value of the expression prior to the first WHEN keyword is tested for equality with the value of the expression that follows each WHEN keyword. If the comparison is true, the statements in the associated THEN clause are executed and processing of the CASE statement ends. If the result is unknown or false, processing continues to the next comparison. If the result does not match any of the comparisons, and an ELSE clause is present, the statements in the ELSE clause are executed.
searched-when-clause: The search-condition following the WHEN keyword is evaluated. If it evaluates to true, the statements in the associated THEN clause are executed and processing of the CASE statement ends. If it evaluates to false, or unknown, the next search-condition is evaluated. If no search-condition evaluates to true and an ELSE clause is present, the statements in the ELSE clause are executed.
When searched-when-clause is used, search-condition cannot contain a fullselect.
SQL-procedure-statement: Specifies a statement to execute. SeeSQL-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 specified in SQL-procedure-statement are executed.
If none of the conditions specified in the WHEN clauses are true and an ELSE is not specified, an error is issued when the statement executes, and the execution of the CASE statement is terminated.
END CASE: Ends a case-statement.

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