Firebird Documentation IndexFirebird 2.5 Language Ref. UpdateMiscellaneous language elements → CASE construct
Firebird Home Firebird Home Prev: Shorthand datetime castsFirebird Documentation IndexUp: Miscellaneous language elementsNext: Data types and subtypes

CASE construct

Table of Contents

Simple CASE
Searched CASE

Available in: DSQL, PSQL

Added in: 1.5

Description: A CASE construct returns exactly one value from a number of possibilities. There are two syntactic variants:

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 to <expr> 1, <expr> 2 etc., until a match is found, upon which the corresponding result is returned. If there is no match and there is an ELSE clause, defaultresult is returned. If there is no match and no ELSE clause, NULL is returned.

The match is determined with the = operator, so if <test-expr> is NULL, it won't match any of the <expr>s, not even those that are NULL.

The results don't have to be literal values: they may also be field or variable names, compound expressions, or NULL literals.

A shorthand form of the simple CASE construct is the DECODE() function, available since Firebird 2.1.

Example: 

select name,
       age,
       case upper(sex)
         when 'M' then 'Male'
         when 'F' then 'Female'
         else 'Unknown'
       end,
       religion
from people

Searched CASE

Syntax: 

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

Here, the <bool_expr>s are tests that give a ternary boolean result: TRUE, FALSE, or NULL. The first expression evaluating to TRUE determines the result. If no expression is TRUE and there is an ELSE clause, defaultresult is returned. If no expression is TRUE and there is no ELSE clause, NULL is returned.

As with the simple CASE, the results don't have to be literal values: they may also be field or variable names, compound expressions, or NULL literals.

Example: 

CanVote = case
            when Age >= 18 then 'Yes'
            when Age <  18 then 'No'
            else 'Unsure'
          end;
Prev: Shorthand datetime castsFirebird Documentation IndexUp: Miscellaneous language elementsNext: Data types and subtypes
Firebird Documentation IndexFirebird 2.5 Language Ref. UpdateMiscellaneous language elements → CASE construct