The CASE statement selects an execution path based on multiple conditions.
>>-+--------+--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-- ; +------------------------|
If none of the conditions specified in the WHEN are true, and an ELSE clause is not specified, an error is issued at runtime, and the execution of the CASE statement is terminated (SQLSTATE 20000).
Nesting of CASE statements: CASE statements that use a simple-when-clause can be nested up to three levels. CASE statements that use a searched-when-clause have no limit to the number of nesting levels.
Considerations for SQLSTATE and SQLCODE 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 expressions 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, then any error returned from the expression is returned.
Example 1: Depending on the value of SQL variable v_workdept, update column DEPTNAME in table DEPARTMENT with the appropriate name.
The following example shows how to do this using the syntax for a simple-when-clause.
CASE v_workdept WHEN 'A00' THEN UPDATE department SET deptname = 'DATA ACCESS 1'; WHEN 'B01' THEN UPDATE department SET deptname = 'DATA ACCESS 2'; ELSE UPDATE department SET deptname = 'DATA ACCESS 3'; END CASE
Example 2: The following example shows how to do this using the syntax for a searched-when-clause:
CASE WHEN v_workdept = 'A00' THEN UPDATE department SET deptname = 'DATA ACCESS 1'; WHEN v_workdept = 'B01' THEN UPDATE department SET deptname = 'DATA ACCESS 2'; ELSE UPDATE department SET deptname = 'DATA ACCESS 3'; END CASE