CASE Expressions
Case expressions provide a decoding capability that allows one expression to be transformed into another. Case expressions can appear anywhere that other forms of expressions can be used.
There are two forms of case expressions:
• Searched
• Simple
Searched CASE
Syntax for the searched case expression is as follows:
CASE
WHEN search_condition1 THEN expr1
WHEN search_expression2 THEN expr2
...
[ELSE expr]
END
The search conditions can be any predicate. Each search condition of each WHEN clause is evaluated in turn. The result of the case is the expression from the THEN clause corresponding to the first WHEN clause whose search condition evaluates to true. If none of the WHEN clause search conditions evaluate as true, the result is the value of the expression from the ELSE clause. If there is no ELSE clause, the result is the null value.
If all the predicates in a searched case expression have the same first operand, then it is easier to write it using the simple form, described next.
Simple CASE
Syntax for a simple case expression is flexible and succinct allowing for an abbreviated CASE expression when there is just a single selection to be used as the first operand of all the comparisons. If this is the case, that first operand is placed directly after the CASE and before the first WHEN clause. The WHEN clauses can be quite powerful.
CASE selector-expr
WHEN comparand-expr,... THEN result-expr
...
[ELSE expr]
END
The selector expression is compared in turn with each comparand expression in each WHEN clause. The case result will be the result expression of the THEN clause corresponding to the first WHEN clause whose comparand expression compares true.
If none of the WHEN expressions match the case selector expression, the result is the value of the expression from the ELSE clause. If there is no ELSE clause, the result is the null value.
Each comparand expression can be either a simple value or a predicate written without the predicate’s first operand. Example “simple” WHEN clauses:
WHEN 1
WHEN >6
WHEN BETWEEN‘a’AND‘f’, BETWEEN‘A’AND‘F’
WHEN IS NULL
WHEN IN(1,2,3,4,5)
WHEN IN(subquery)
WHEN IS INTEGER, IS FLOAT
WHEN LIKE pattern WITHOUT CASE ESCAPE char
WHEN >ALL(subquery)
DECODE Function
DECODE performs the same function as the simple case expression.
DECODE
DECODE(source_value, match_value1, decode_value1, match_value2, decode_value2,..., match_valuen, decode_valuen [, else_value])
Operand type and Result type: All data types, but the types of source_value,match_value1, match_value2, … must be union compatible, and the types of decode_value1, decode_value2, …, else_value must also be union compatible.
Compares source_value successively to match_value1, match_value2, …, match_valuen. The result is the decode_valuei of the first matching match_valuei. If none of the match_valuei have the same value as source_value, the result of the function is else_value.
Examples:
1. Decode the value state_code. If state_code is 'ak', then return 'Alaska'; if state_code is 'al', then return 'Alabama'; and so on; otherwise return "unknown".
DECODE(state_code, 'ak', 'Alaska',
'al', 'Alabama',
. . . ,
'wy', 'Wyoming',
'unknown') AS state_name;
2. Decode the value sex:
SELECT name, DECODE(sex, 'm', 'male', 'f', 'female', 'unknown') FROM person;
IF, NULLIF, and COALESCE Functions
IF, NULLIF, and COALESCE are derivative functions of the case expression and can be defined in terms of the case expression.
IF
Returns the second parameter if the first evaluates as true; otherwise it returns any third, if present. The first expression must be a predicate.
It can be represented as follows:
IF(expr1, expr2)
is the same as:
CASE WHEN expr1 THEN expr2 ELSE NULL END
and
IF(expr1, expr2, expr3)
is the same as:
CASE WHEN expr1 THEN expr2 ELSE expr3 END
Example: IF (a = 1, b IS FALSE)
NULLIF
Returns the null value if its two parameters are equal; otherwise it returns the first parameter value. It can be represented as follows:
NULLIF(expr1, expr2)
is the same as:
CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END
COALESCE
Returns the first non-null value of an arbitrary list of parameters. It can be represented as follows:
COALESCE(expr1, expr2)
is the same as:
CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END
and
COALESCE(expr1, expr2, ..., exprn)
is the same as:
CASE WHEN expr1 IS NOT NULL THEN expr1
ELSE COALESCE(expr2, ..., exprn)
GREATEST, GREATER, LEAST, LESSER Functions
GREATEST
GREATEST(v1,v2...vN)
Operand type: Any
Result type: Any
Returns the greatest value in a list of one or more values (v1 through vN). Return type is based on that of the first parameter with a size or precision suitable to represent each of the parameters. If all of the values v1 through vN are NULL, then returns NULL.
GREATER
GREATER(v1,v2…vN)
Operand type: Any
Result type: Any
Returns the greatest value in a list of one or more values (v1 through vN). Return type is based on that of the first parameter with a size or precision suitable to represent each of the parameters. If any of the values v1 through vN are NULL, then returns NULL.
LEAST
LEAST(v1,v2...vN)
Operand type: Any
Result type: Any
Returns the least value in a list of one or more values (v1 through vN). Return type is based on that of the first parameter with a size or precision suitable to represent each of the parameters. If all of the values v1 through vN are NULL, then returns NULL.
LESSER
LESSER(v1,v2...vN)
Operand type: Any
Result type: Any
Returns the least value in a list of one or more values (v1 through vN). Return type is based on that of the first parameter with a size or precision suitable to represent each of the parameters. If any of the values v1 through vN are NULL, then returns NULL.