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.
CASEstatement 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
The searched case involves the evaluation for truth of a number of alternative search conditions, each following a
In each form of the
CASEit is the first
WHENclause 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
THENclause for each
If none of the
WHENclauses evaluates to true, the SQL statements following the
ELSEclause are executed. If none of the
WHENclauses evaluates to true and there is no
ELSEclause, an exception condition is raised to indicate that a case was not found.
ELSEclause supporting an empty compound statement will avoid an exception condition being raised, in cases where no `else' action is required, when none of the
WHENalternatives 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
CASEstatement 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; 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;
Mimer Information Technology AB
Phone: +46 18 780 92 00