4.1.3. Conditional Expressions

A conditional expression is one that returns different values according to how a certain condition is met. It is composed by applying a conditional function construct, of which Firebird supports several. This section describes only one conditional expression construct: CASE. All other conditional expressions apply internal functions derived from CASE and are described in Conditional Functions.

CASE

Available

DSQL, PSQL

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

• The simple CASE, comparable to a case construct in Pascal or a switch in C

• The searched CASE, which works like a series of “if …​ else if …​ else if” clauses.

Simple CASE

Syntax

…
CASE <test-expr>
  WHEN <expr> THEN <result>
  [WHEN <expr> THEN <result> ...]
  [ELSE <defaultresult>]
END
…

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.

Example

SELECT
  NAME,
  AGE,
  CASE UPPER(SEX)
    WHEN 'M' THEN 'Male'
    WHEN 'F' THEN 'Female'
    ELSE 'Unknown'
  END GENDER,
RELIGION
    FROM PEOPLE

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

Searched CASE

Syntax

CASE
  WHEN <bool_expr> THEN <result>
  [WHEN <bool_expr> THEN <result> …]
  [ELSE <defaultresult>]
END

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 CASE construct, the result need not be a literal value: it might be a field or variable name, a compound expression, or be NULL.

Example

CANVOTE = CASE
  WHEN AGE >= 18 THEN 'Yes'
  WHEN AGE < 18 THEN 'No'
  ELSE 'Unsure'
END