Available

DSQL, PSQL

The CASE construct returns a single value from a number of possible ones. Two syntactic variants are supported:

Simple

Syntax

When this variant is used, test-expr is compared expr 1, expr 2 etc., until a match is found and the corresponding result is returned. If no match is found, defaultresult from the optional ELSE clause is returned. If there are no matches and no ELSE clause, NULL is returned.

The matching works identically to the “=” operator. That is, if test-expr is NULL, it does not match any expr, not even an expression that resolves to NULL.

The returned result does not have to be a literal value: it might be a field or variable name, compound expression or NULL literal.

A short form of the simple CASE construct is the DECODE function.

Searched

Syntax

The bool_expr expression is one that gives a ternary logical result: TRUE, FALSE or NULL. The first expression to return TRUE determines the result. If no expressions return TRUE, defaultresult from the optional ELSE clause is returned as the result. If no expressions return TRUE and there is no ELSE clause, the result will be NULL.

As with the simple construct, the result need not be a literal value: it might be a field or variable name, a compound expression, or be NULL.