Control Statements › CASE

CASE

The CASE statement selects different execution paths depending on the evaluation of one or more value-expressions.

Syntax

►►──── CASE ───┬── simple-case-when-clause ───┬────────────────────────────────►
               └── searched-case-when-clause ─┘

 ►──┬───────────────────────────────────────────┬─ END CASE ───────────────────►◄
    │            ┌────────────────────────────┐ │
    └─── ELSE ── ▼ ─ procedure-statement ─ ; ─┴─┘

Expansion of simple-case-when-clause

►►──── value-expression ─────────────────────────────────────────────────────►

     ┌────────────────────────────────────────────────────────────────────┐
     │                                     ┌────────────────────────────┐ │
 ►── ▼ ─ WHEN ── value-expression ─ THEN ─ ▼ ─ procedure-statement ─ ; ─┴─┴──►◄

Expansion of searched-case-when-clause

    ┌────────────────────────────────────────────────────────────────────┐
    │                                     ┌────────────────────────────┐ │
►►─ ▼ ─ WHEN ── search-condition ─ THEN ─ ▼ ─ procedure-statement ─ ; ─┴─┴────►◄

Parameters

Parameters for Expansion of simple-case-when-clause

CASE value-expression

Specifies the value expression whose outcome is compared to the outcomes of the value-expressions in the WHEN clauses.

WHEN value-expression

Specifies a value expression whose outcome is compared to the outcome of the CASE value-expression. If the two values are equal, the group of statements specified in the corresponding THEN is executed.

THEN procedure-statement

Identifies the group of statements to be executed when the value expressions of the CASE and WHEN clauses are equal.

Parameters for Expansion of searched-case-when-clause

CASE WHEN

Identifies the CASE as a searched case.

WHEN search-condition

Specifies the search condition whose outcome, if true, results in the execution of the group of statements specified by the THEN clause.

THEN procedure-statement

Identifies the group of statements executed when the search-condition in the corresponding WHEN clause evaluates to true.

ELSE procedure-statement END CASE

Specifies the group of statements to be executed when none of the THEN group of statements has been executed because of the evaluation and comparison of the value-expression's and search-condition's. This clause can be specified for both simple and searched case statements.

Usage

SQL Exceptions

If an ELSE clause is not specified and none of the THEN group of statements has been executed because of the outcome of evaluation of the value expressions and search conditions, an SQL exception is raised.

Examples

The first example demonstrates the use of a simple-case-when-clause.

set options command delimiter '++';
create function USER01.TCASE1
  ( TITLE     varchar(40) with default
  , P_EMP_ID  unsigned numeric(4)
  ) RETURNS   varchar(30)
    external name TCASE1 language SQL
begin not atomic
  /*
  ** Function selects an employee with the given EMP_ID and swaps
  ** the first_name value 'James' with 'Jim'.
  ** Returns a message text with the outcome of the execution
  */
  declare MY_STATUS varchar(30);
  declare LOC_FNAME char(20) default ' ';

  select EMP_FNAME into LOC_FNAME
    from DEMOEMPL.EMPLOYEE
   where EMP_ID = P_EMP_ID;

  case LOC_FNAME
    when 'James'
      then update DEMOEMPL.EMPLOYEE set EMP_FNAME = 'Jim'
            where EMP_ID = P_EMP_ID;
           set MY_STATUS = 'James->JIM';
    when 'Jim'
      then update DEMOEMPL.EMPLOYEE set EMP_FNAME = 'James'
            where EMP_ID = P_EMP_ID;
           set MY_STATUS = 'Jim->James';

    when 'Thomas'
      then update DEMOEMPL.EMPLOYEE set EMP_FNAME = 'Thomas'
            where EMP_ID = P_EMP_ID;
           set MY_STATUS = 'Dummy update';
    else set MY_STATUS = 'No Changes';
  end case;
  return MY_STATUS;
end
++
set options command delimiter default++
commit;

select USER01.TCASE1('TCASE1', 1034)from SYSCA.SINGLETON_NULL;
*+
*+ USER_FUNC
*+ ---------
*+ Jim->James

The second example demonstrates the searched-case-when-clause. It is functionally equivalent with the example of simple-case-when-clause.

set options command delimiter '++';
create function USER01.TCASESR1
  ( TITLE     varchar(40) with default
  , P_EMP_ID  unsigned numeric(4)
  ) RETURNS   varchar(30)
    external name TCASESR1 language SQL
begin not atomic
  /*
  ** Function selects an employee with the given EMP_ID and
  ** does some conditional updates.
  ** Returns a message text with the outcome of the execution
  */
  declare MY_STATUS varchar(30);
  declare LOC_FNAME char(20) default ' ';
  declare LOC_LNAME char(20) default ' ';

  select EMP_FNAME, EMP_LNAME into LOC_FNAME, LOC_LNAME
    from DEMOEMPL.EMPLOYEE where EMP_ID = P_EMP_ID;

  case
    when LOC_FNAME = 'James'
      then update DEMOEMPL.EMPLOYEE set EMP_FNAME = 'Jim'
            Where EMP_ID = P_EMP_ID;
           set MY_STATUS = 'James->JIM';
    when LOC_FNAME = 'Jim' and LOC_LNAME = 'Gallway'
      then update DEMOEMPL.EMPLOYEE set EMP_FNAME = 'James'
            Where EMP_ID = P_EMP_ID;
           set MY_STATUS = 'Jim->James';
    when LOC_LNAME = 'Van der Bilck'
      then update DEMOEMPL.EMPLOYEE set EMP_LNAME = 'Vanderbilck'
            Where EMP_ID = P_EMP_ID;
           set MY_STATUS = 'Van der Bilck->Vanderbilck';
    else set MY_STATUS = 'No Changes';
  end case;

  return MY_STATUS;
end
++
set options command delimiter default++