AntDB-Oracle Compatibility Developer’s Manual P3–65

CASE

CASE expressions in SQL are a class of conditional expressions that function similarly to if/else statements in other languages.

CASE WHEN condition THEN result

[ WHEN ... ]

[ ELSE result ]

END

The CASE clause can be used anywhere the expression appears. condition is an expression that returns a result of type BOOLEAN. If the return result is true, then the value of the CASE expression is the result following the condition. If the result is false, then the WHEN clause after the CASE expression is searched in the same way. If the WHEN condition is not true, then the value of the CASE expression is the result in the ELSE clause. If no ELSE clause is used and no condition is matched, then the result is returned as null.

Example:

SELECT * FROM test;

a
---
1
2
3
(3 rows)

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
(3 rows)

All data types of result expressions must be able to be converted to a single output type.

The following simplified CASE expression is another specific use of the above common type expression.

CASE expression

WHEN value THEN result

[ WHEN ... ]

[ ELSE result ]

END

The expression is first calculated and then compared with all the values specified in the WHEN clause until an equal condition is found. If no match is found, then the result (or a null value) in the ELSE clause is returned.

We can rewrite the above example using the simplified CASE syntax.

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
(3 rows)

CASE expressions do not compute sub-expressions that do not require a confirmed result. For example, here is an allowed method to avoid the divide by zero operation.

SELECT ...  WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;