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:
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.
For information on the case-expression, which provides a mechanism for conditionally selecting values, see CASE Expression.
For a list of procedural-sql-statements, see Procedural SQL Statements.
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).
Simple CASE statement:
DECLARE Y INTEGER;
WHEN 1 THEN ...
WHEN 2 THEN ...
WHEN 3 THEN ...
Searched CASE statement:
WHEN EXISTS (SELECT * FROM BILL) THEN ...
WHEN X > 0 OR Y = 1 THEN ...
Features outside core
Feature P002, “Computational completeness”.