The CASE statement executes SQL statements depending on specific values (unqualified CASE statement) or conditions (CASE statement with search condition).
The CASE statement may only be specified in a routine, i.e. in the context of a CREATE PROCEDURE or CREATE FUNCTION statement. Routines and their use in SESAM/SQL are described in detail in chapter "Routines".
The CASE statement is a non-atomic SQL statement, i.e. further (atomic or non-atomic) SQL statements can occur in it.
If the search_condition or an expression of a CASE statement corresponds to a table, the authorization identifier which creates the routine using CREATE PROCEDURE or CREATE FUNCTION must have the SELECT privilege for this table.
Execution information
The CASE statement is a non-atomic statement:
If the CASE statement is part of a COMPOUND statement, the rules described there apply, in particular the exception routines defined there.
If the CASE statement is not part of a COMPOUND statement and one of the SQL statements reports an SQLSTATE, it is possible that only the updates of this SQL statement will be undone. The CASE statement and the routine in which it is contained are aborted. The SQL statement in which the routine was used returns the SQLSTATE concerned.
See also
CREATE PROCEDURE, CREATE FUNCTION
Format of the simple CASE statement
CASE expressionx
WHEN expression1 , ... THEN routine_sql_statement; [ routine_sql_statement; ] ...
...
[ ELSE routine_sql_statement; [ routine_sql_statement; ] ... ]
END CASE
expression
Expression that returns an alphanumeric, national, numeric or time value when evaluated.
It cannot be a multiple value with a dimension greater than 1.
expression may not include host variables.
A column may only be specified in a subquery.
The values of expressionx and expression1, ... must have compatible data types (see section "Compatibility between data types").
routine_sql_statement
SQL statement which is to be executed in the THEN or ELSE clause depending on the values of expressionx and expression1, ... .
An SQL statement is concluded with a ";" (semicolon).
Multiple SQL statements can be specified one after the other. They are executed in the order specified.
No privileges are checked before an SQL statement is executed.
An SQL statement in a routine may access the parameters of the routine and (if the statement is part of a COMPOUND statement) local variables, but not host variables.
The syntax and meaning of routine_sql_statement are described centrally in section "SQL statements in routines". The SQL statements named there may not be used.
Execution information
expressionx of the CASE statement is calculated.
The WHEN clauses are evaluated from top to bottom.
The expressions expression1,... of the WHEN clauses are calculated from left to right.
When a value of an expression calculated in this way corresponds to the value of expressionx, the associated THEN branch is executed, and the CASE statement is subsequently terminated.
If none of the calculated values corresponds to expressionx but an ELSE branch exists, the ELSE branch of the CASE statement is executed, and the CASE statement is subsequently terminated.
CASE statement is terminated with SQLSTATE '20000'.
Example
Simple CASE statement for calculating the public holiday allowance in wages.
CASE MOD(JULIAN_DAY_OF_DATE(CURRENT_DATE),7)
WHEN 0,1,2,3,4 /* today is a normal workday */
THEN UPDATE pay_scale SET pay = time_pay;
WHEN 5 /* today is Saturday, 25% supplement */
THEN UPDATE pay_scale SET pay = time_pay * 1.25;
WHEN 6 /* today is Sunday, 50% supplement */
THEN UPDATE pay_scale SET pay = time_pay * 1.50;
END CASE
The CASE statement above could also be replaced by an UPDATE statement with an appropriate case_expression.
UPDATE pay-scale
SET pay = time_pay * CASE MOD(JULIAN_DAY_OF_DATE(CURRENT_DATE),7)
WHEN 0,1,2,3,4 /* today is a normal workday */
THEN 1.00
WHEN 5 /* today is Saturday, 25% supplement */
THEN 1.25
WHEN 6 /* today is Sunday, 50% supplement */
THEN 1.50
END
Format of the CASE statement with search condition
CASE WHEN search_condition THEN THEN routine_sql_statement; [ routine_sql_statement; ] ...
...
[ ELSE routine_sql_statement; [ routine_sql_statement; ] ...]
END CASE
search_condition
Search condition that returns a truth value when evaluated
If the result of the search condition is “unknown”, no SQL statement is executed in the THEN clause.
routine_sql_statement
See "Format of the simple CASE statement".
Execution information
The WHEN clauses are evaluated from top to bottom.
The search_condition of the WHEN clause is evaluated.
When such a calculated search condition returns the truth value TRUE, the associated THEN branch is executed, and the CASE statement is subsequently terminated.
If none of the calculated search conditions returns the truth value TRUE but an ELSE branch exists, the ELSE branch of the CASE statement is executed, and the CASE statement is subsequently terminated.
If none of the calculated search conditions returns the truth value TRUE and no ELSE branch exists, the CASE statement is terminated with SQLSTATE '20000'.
Example
CASE statement with search condition.
CASE
WHEN (EXISTS(select * from T1 where cola = 17))
THEN update T1 set colb = colb * 1.05;
WHEN (EXISTS(select * from T2 where colx = 27))
THEN insert into T2 (pk, coly) values (*, 423);
END CASE