Allows sequences of SQL statements to be selected for execution based on search or comparison criteria.
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; 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;
SQL/PSM YES Fully compliant.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40