A CASE expression with a search condition has the following syntax:
case_expression ::=
CASE
WHEN search_condition THEN { expression | NULL }
...
[ELSE { expression | NULL }]
END
search_condition
Search condition that returns a truth value when evaluated
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 must be contained in the THEN clause, the ELSE clause or in both.
The data types of the values of expression in the THEN clauses and in the ELSE clause must be compatible (see section "Compatibility between data types").
Result
The result of the CASE expression is contained in the THEN clause whose associated search_condition is the first to return the truth value. The THEN clause contains the value of the expression assigned to the THEN clause or the NULL value. The WHEN clauses are processed from left to right.
If no search_condition returns the truth value true, the result is the contents of the ELSE clause, i.e. the value of the expression assigned to the ELSE clause or the NULL value. If you do not specify the ELSE clause, the default applies (NULL).
The data type of a CASE expression with a search condition is derived from the data types of the values of expression contained in the THEN clauses and the ELSE clause, as follows:
Each expression has the data type CHAR or NCHAR respectively:The value of the CASE expression is that with the data type CHAR or NCHAR respectively and the greatest length.
At least one value of expression has the data type VARCHAR or NVARCHAR respectively:The value of the CASE expression is that with the data type VARCHAR or NVARCHAR respectively and the greatest or greatest maximum length.
Each expression is of the type integer or fixed-point number (INT, SMALLINT, NUMERIC, DEC):The value of the CASE expression has the data type integer or fixed-point number.
The number of decimal places is the greatest number of decimal places among the various values of expression.
The total number of places is the greatest number of places before the decimal point plus the greatest number of decimal places among the different values of expression, but not more than 31.
At least one value of expression is of the type floating-point number (REAL, DOUBLE PRECISION, FLOAT); the others have any other numeric data type:The value of the CASE expression has the data type DOUBLE PRECISION.
Each expression has the time data type:All values must have the same time data type, and the value of the CASE expression also has this data type.
Example
Sort the items in the ITEMS table in accordance with the urgency with which they need to be ordered.
SELECT item_num, item_name,
CASE
WHEN stock > min_stock THEN 'O.K.'
WHEN stock = min_stock THEN 'order soon'
WHEN stock > min_stock * 0.5 THEN 'order now'
ELSE 'order urgently'
END
FROM items