SAND CDBMS SQL Reference Guide
CASE Expressions

 

The CASE statement is a conditional expression that can be used anywhere a value expression is used. It allows for the evaluation of multiple conditions, returning a specified value when a condition is true, or a default value when none of the conditions is true.

The following syntax diagrams describe the CASE statement:


As indicated in the syntax diagrams, there are two types of CASE statements: simple and searched.


Simple CASE

In the simple CASE, a value expression follows the CASE keyword. This expression is tested for equality against the value expression in each WHEN clause. If a truth condition is encountered, testing of any remaining conditions is halted and the value expression for the current THEN clause is returned. If no comparison evaluates to true, then the default value expression in the ELSE clause is returned. If there is no ELSE clause, a null value is returned. At least one of the THEN clauses must specify a value other than NULL. All comparisons must involve compatible data types.

Example

SELECT lastname,
       CASE dept
          WHEN 'A' THEN 'Administration'
          WHEN 'D' THEN 'Development'
          WHEN 'Q' THEN 'Quality Assurance'
          WHEN 'T' THEN 'Technical Writing'
          ELSE '(unknown department)'
       END
FROM employees;

In this example, the employees table is searched for employee last name and department. But instead of returning the single character that represents a department, a CASE statement is set up to return the full (or default) department name. If the dept value is 'A', the string 'Administration' is returned; if the dept value is 'D', 'Development' is returned; and so on. If the dept value matches none of the single characters tested in the CASE statement, the default string '(unknown department)' is returned instead.


Searched CASE

In contrast to the simple CASE, the searched CASE does not use a single test expression to compare for equality. Instead, the searched CASE permits any Boolean condition, using any comparison operator(s), to appear in each WHEN clause. As in the simple CASE, the first condition to evaluate to true returns the value expression in the associated THEN clause. If no comparison evaluates to true, then the default value expression in the ELSE clause is returned. If there is no ELSE clause, a null value is returned. At least one of the THEN clauses must specify a value other than NULL. All comparisons must involve compatible data types.

Note that a simple CASE can always be rewritten as a searched CASE in the following manner:

Simple CASE:

CASE <value-exp-0>
WHEN <compare-value-exp-1> THEN <return-value-exp-1>
WHEN <compare-value-exp-2> THEN <return-value-exp-2>
...
WHEN <compare-value-exp-n> THEN <return-value-exp-n>
ELSE <value-exp-x>
END


Searched CASE:

CASE
WHEN <value-exp-0> = <compare-value-exp-1> THEN <return-value-exp-1>
WHEN <value-exp-0> = <compare-value-exp-2> THEN <return-value-exp-2>
...
WHEN <value-exp-0> = <compare-value-exp-n> THEN <return-value-exp-n>
ELSE <value-exp-x>
END


Example

SELECT name,
       CASE
          WHEN age < 0 THEN '(unknown)'
          WHEN age < 18 THEN 'Youth'
          WHEN age >= 18 AND age < 65 THEN 'Adult'
          ELSE 'Senior'
       END
FROM people;

In this example, the people table is searched for name; as well, a character string that varies according to the age of the individual is returned with the name. Since ranges of ages are tested in the CASE statement in this example, as opposed to exact ages, a searched CASE statement is preferable to a simple CASE. Here, if the age value is less than zero, the character string '(unknown)' is returned; if the age is between 0 and 17 inclusive, 'Youth' is returned; and if the age is between 18 and 64 inclusive, 'Adult' is returned. If the age value does not fall into any of those ranges, then it must be 65 or greater, so the default value 'Senior' is returned instead. Note that the order of processing of the conditions can sometimes be important. In the above example, the condition "age < 0" must appear before "age < 18" in the CASE structure, otherwise a value of -1 will return will return 'Youth' instead of '(unknown)'.


Related Topics: