|
|
|
|
|
|
The CASE expression is a generic conditional expression that can be used wherever an expression is valid. It is similar to case and if/then/else statements in other languages.
Syntax (Form 1)
CASE
WHEN condition THEN result
[ WHEN condition THEN result ]...
[ ELSE result ]
END
Semantics
condition |
is an expression that returns a boolean (true/false) result. If the result is false, subsequent WHEN clauses are evaluated in the same manner. |
result |
specifies the value to return when the associated condition is true. |
ELSE result |
If no condition is true then the value of the CASE expression is the result in the ELSE clause. If the ELSE clause is omitted and no condition matches, the result is null. |
Syntax (Form 2)
CASE expression
WHEN value THEN result
[ WHEN value THEN result ]...
[ ELSE result ]
END
Semantics
expression |
is an expression that is evaluated and compared to all the value specifications in the WHEN clauses until one is found that is equal. |
value |
specifies a value to compare to the expression. |
result |
specifies the value to return when the expression is equal to the specified value. |
ELSE result |
specifies the value to return when the expression is not equal to any value; if no ELSE clause is specified, the value returned is null. |
Notes
Examples
SELECT * FROM test;
a
---
1
2
3
SELECT a,
CASE WHEN a=1 THEN 'one'
WHEN a=2 THEN 'two'
ELSE 'other'
END
FROM test;
a | case
---+-------
1 | one
2 | two
3 | other
SELECT a,
CASE a WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'other'
END
FROM test;
a | case
---+-------
1 | one
2 | two
3 | other
Special Example
A CASE expression does not evaluate subexpressions that are not needed to determine the result. You can use this behavior to avoid division-by-zero errors:
SELECT x
FROM T1
WHERE
CASE WHEN x <> 0 THEN y/x > 1.5
ELSE false
END;