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
Description
- label
- Specifies the label for the CASE statement. The label name cannot be the same as the routine name, advanced trigger name, or another label within the same scope. For additional information, see References to SQL 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 (SQL PL).
- 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