The general CASE function (searched_case_function) is a special function that analyzes a quantity of search conditions to determine a result_expression.
Syntax
<searched_case_function>
::=
CASE
WHEN <search_condition> THEN <result_expression>
[...]
[ELSE <default_expression>])
END
<result_expression>
::= <expression>
<default_expression> ::= <expression>
u(i) |
search_condition |
Search conditions u(i). All search conditions without ROWNO Predicate are permissible. |
v(i) |
result_expression |
Value v(i) that is to be accepted, if the search condition u(i) is true |
z |
default_expression |
Value z, optional default value |
CASE checks the search conditions u(i) in succession. As soon as a search condition that is true is found, the result of the general CASE function is the value v(i) that belongs to u(i).
The data types of v(i) and z must be comparable.
If no search condition that is true is found, CASE returns the result of z. If z is not specified, the NULL value is the result of CASE.
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20 THEN 'Coffee Table
Title'
ELSE 'Expensive book!'
END
See also:
Simple CASE function (simple_case_function)