Executes one of a sequence of alternate statement blocks in a
compound statement of a multistatement procedure.
1 – Environment
You can use the simple CASE control statement in a compound
statement of a multistatement procedure:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
(B)0[m[1mcase-statement = [m [1m [m [1;4mCASE[m[1m value-expr qqqqqqqqk [m [1mlqqqqqqqqqqqqqqqqqqqqqqqj [m [1mmwq> [1;4mWHEN[m[1m qwqw> <literal> qwqwq> [1;4mTHEN[m[1m qwq> compound-use-statement qwqwqk [m [1m x x m> [1;4mNULL[m[1m qqqqqqj x mqqqqqqqqqqqq<qqqqqqqqqqqqqqj x x [m [1m x mqqqqq , <qqqqqqqqj x x [m [1m mqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x [m [1m lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m [1m mwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq> [1;4mEND[m[1m [1;4mCASE[m[1m qqq> [m [1m mqq> [1;4mELSE[m[1m qwq> compound-use-statement qwqj [m [1m mqqqqqqqqqqqq<qqqqqqqqqqqqqqj [m
3 – Arguments
3.1 – CASE
An expression that evaluates to a single value. SQL compares the
CASE clause value expression with each WHEN clause literal value
in the WHEN clauses until it finds a match.
The value expression cannot contain a column specification that
is not part of a column select expression.
See the Value_Expressions HELP topic for a complete description
of the variety of value expressions that SQL provides.
3.2 – ELSE
Executes a set of SQL statements when SQL cannot find a WHEN
clause that matches the value expression in the CASE clause.
See the Compound_Statement HELP topic for a description of the
SQL statements that are valid in a compound statement.
3.3 – THEN
Executes the set of SQL statements associated with the first
WHEN clause in which its argument value matches the CASE value
expression.
3.4 – WHEN
Syntax options:
WHEN literal | WHEN NULL
The literal or NULL value of the WHEN clause that SQL compares
with the value expression of the CASE clause. Most CASE control
statements include a set of WHEN clauses.
When the values of the WHEN and CASE clauses match, SQL executes
the SQL statements associated with that WHEN clause. Control then
drops out of the CASE control statement and returns to the next
SQL statement after the END CASE clause.
4 – Examples
Example 1: Using the CASE control statement
char x[11];
long x_ind;
EXEC SQL
DECLARE ALIAS FOR FILENAME personnel ;
EXEC SQL
BEGIN
CASE :x INDICATOR :x_ind
WHEN 'Abrams' THEN
DELETE FROM employees WHERE . . . ;
WHEN NULL THEN
DELETE FROM employees WHERE . . . ;
ELSE
DELETE FROM employees WHERE . . . ;
END CASE ;
END ;
Example 2: Using a List of Literal Values with the Case Statement
SQL> DECLARE :CODE CHAR(4);
SQL> BEGIN
cont> JOB_LOOP:
cont> FOR :JOBFOR
cont> AS EACH ROW OF
cont> SELECT * FROM JOBS JOB
cont> DO
cont> SET :CODE = :jobfor.JOB_CODE;
cont> CASE :CODE
cont> WHEN 'ASCK' THEN
cont> UPDATE JOBS
cont> SET MINIMUM_SALARY=10000
cont> WHERE JOB_CODE = :code;
cont> WHEN 'ADMN', 'JNTR', 'SCTR' THEN
cont> UPDATE JOBS
cont> SET MINIMUM_SALARY=15000
cont> WHERE JOB_CODE = :code;
cont> ELSE
cont> UPDATE JOBS
cont> SET MINIMUM_SALARY=:jobfor.MINIMUM_SALARY*1.1
cont> WHERE JOB_CODE=:code;
cont> END CASE;
cont> END FOR;
cont> END;
SQL>