CASE

Edit online

The CASE function selects an execution path based on the evaluation of one or more conditions. A CASE statement operates in the same way as a CASE expression.

simple-when-clause:

searched-when-clause:

Description

CASE: Begins a case-expression.
simple-when-clause: Specifies the expression prior to the first WHEN keyword that is tested for equality with the value of each expression that follows the WHEN keyword, and the result to be executed when those expressions are equal. If the comparison is true, the THEN statement is executed. If the result is unknown or false, processing continues to the next expression or the ELSE statement.
The data type of the expression prior to the first WHEN keyword must be comparable to the data types of each expression that follows the WHEN keywords.
searched-when-clause: Specifies the search-condition that is applied to each row or group of table data presented for evaluation, and the result when that condition is true. search-condition cannot contain a fullselect. If the search condition is true, the THEN statement is executed. If the condition is unknown or false, processing continues to the next search condition or the ELSE statement.
SQL-procedure-statement: Specifies a statement that follows the THEN and ELSE keyword. The statement specifies the result of a searched-when-clause or a simple-when-clause that is true, or the result if no case is true.
search-condition: Specifies a condition that is true, false, or unknown about a row or group of table data.
ELSE SQL-procedure-statement: If none of the conditions specified in the simple-when-clause or searched-when-clause are true, the statements in the else-clause are executed.
If none of the conditions specified in the WHEN clause are true and an ELSE clause is not specified, an error is returned at run time, and the execution of the CASE statement is terminated.
END CASE: Ends a case-statement.

Note:

If none of the conditions specified in the WHEN clause are true and an ELSE clause is not specified, an error is returned at run time, and the execution of the CASE statement is terminated.

CASE statements that use a simple case statement WHEN clause can be nested up to three levels. CASE statements that use a searched statement WHEN clause have no limit to the number of nesting levels.

Examples:

The following example assigns the experience level of 1 to employees with age <40 and the experience level of 2 to employees with age >40 from the table EMPLOYEE.


SELECT EMPL_NAME,
CASE
WHEN AGE > 40 THEN 'LEVEL 2'
WHEN AGE < 40 THEN 'LEVEL 1'
END AS EXP_LEVEL,
FROM EMPLOYEE;

The above example returns the following:

Table 1. Case
EMPL_NAME	EXP_LEVEL
BUMRAH	LEVEL 2
CEASAR	LEVEL 2
ELICA	LEVEL 1
DEV	LEVEL 1
NEWTON	LEVEL 2
SAMUEL	LEVEL 2
ELICA