1  CASE_Simple
   Executes one of a sequence of alternate statement blocks in a
   compound statement of a multistatement procedure.
 

2  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

 case-statement =
 
 CASE value-expr --------+
 +-----------------------+
 ++-> WHEN -+-+> <literal> -+-+-> THEN -+-> compound-use-statement -+-+-+
  |         | +> NULL ------+ |         +------------<--------------+ | |
  |         +----- , <--------+                                       | |
  +---------------------------------<---------------------------------+ |
   +--------------------------------<-----------------------------------+
   ++----------------------------------------+--> END CASE --->
    +--> ELSE -+-> compound-use-statement -+-+
               +------------<--------------+

 

2  Arguments
 

3  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  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  THEN

   Executes the set of SQL statements associated with the first
   WHEN clause in which its argument value matches the CASE value
   expression.
 

3  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.
 

2  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>