CASE

Allows sequences of SQL statements to be selected for execution based on search or comparison criteria.

Note:A CASE statement is not the same as a case expression. A CASE statement is for conditional execution of SQL statements, while a CASE expression has a return value, and is typically used in SQL queries. See CASE Expression.

 

where simple-case-when-clause is:

 

where searched-case-when-clause is:

 

Usage

Procedural.

Description

The CASE statement provides a mechanism for conditional execution of SQL statements. It exists in two forms: the simple case and the searched case.

The simple case involves an equality comparison between one expression and a number of alternative expressions, each following a WHEN clause.

The searched case involves the evaluation for truth of a number of alternative search conditions, each following a WHEN clause.

In each form of the CASE it is the first WHEN clause to evaluate to true, working from the top down, that determines which sequence of SQL statements will be executed.

There may be one or more SQL statements following the THEN clause for each WHEN.

If none of the WHEN clauses evaluates to true, the SQL statements following the ELSE clause are executed. If none of the WHEN clauses evaluates to true and there is no ELSE clause, an exception condition is raised to indicate that a case was not found.

Providing an ELSE clause supporting an empty compound statement will avoid an exception condition being raised, in cases where no ‘else’ action is required, when none of the WHEN alternatives evaluates to true.

case-expression

For information on the case-expression, which provides a mechanism for conditionally selecting values, see CASE Expression.

procedural-sql-statements

For a list of procedural-sql-statements, see Procedural SQL Statements.

Notes

Flow of control leaves the CASE statement as soon as the SQL statements following the selected THEN, or the ELSE, have been executed (i.e. there is no fall-through as is found in a case statement in, for example, the C programming language).

Examples

Simple CASE statement:

DECLARE Y INTEGER;

 

CASE Y

 WHEN 1 THEN ...

 WHEN 2 THEN ...

 WHEN 3 THEN ...

 ELSE  ...

END CASE;

Searched CASE statement:

CASE

 WHEN EXISTS (SELECT * FROM BILL) THEN ...

  WHEN X > 0 OR Y = 1 THEN ...

ELSE ...

END CASE;

Standard Compliance