FirebirdSQL logo
  • Anglaiskeyboard_arrow_down

CASE expression

Firebird introduced the CASE construct in version 1.5, with two syntactic variants.The first one is called the simple syntax:

case <expression>
  when <exp1> then <result1>
  when <exp2> then <result2>
  ...
  [else <defaultresult>]
end

This one works more or less like a Pascal case or a C switch construct: <expression> is compared to <exp1>, <exp2> etc., until a match is found, in which case 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.

It is important to know that the comparisons are done with the ‘=’ operator, so a null <expression> will not match a null <expN>.If <expression> is NULL, the only way to get a non-NULL result is via the ELSE clause.

It is OK to specify NULL (or any other valid NULL expression) as a result.

The second, or searched syntax is:

case
  when <condition1> then <result1>
  when <condition2> then <result2>
  ...
  [else <defaultresult>]
end

Here, the <conditionN>s are tests that give a ternary boolean result: true, false, or NULL.Once again, only true is good enough, so a condition like “A = 3” — or even “A = null” — is not satisfied when A is NULL.Remember though that “IS [NOT] NULL” never returns NULL: if A is NULL, the condition “A is null” returns true and the corresponding <resultN> will be returned.In Firebird 2+ you can also use “IS [NOT] DISTINCT FROM” in your conditions — this operator too will never return NULL.

WHILE loops

When evaluating the condition of a WHILE loop, NULL has the same effect as in an IF statement: if the condition resolves to NULL, the loop is not (re)entered — just as if it were false.Again, watch out with inversion using NOT: a condition like

while ( Counter > 12 ) do

will skip the loop block if Counter is NULL, which is probably what you want, but:

while ( not Counter > 12 ) do

will also skip if Counter is NULL.Maybe this is also exactly what you want — just be aware that these seemingly complementary tests both exclude NULL counters.

FOR loops

To avoid any possible confusion, let us emphasise here that FOR loops in Firebird PSQL have a totally different function than WHILE loops, or for loops in general programming languages.Firebird FOR loops have the form:

for <select-statement> into <var-list> do <code-block>

and they will keep executing the code block until all the rows from the result set have been retrieved, unless an exception occurs or a BREAK, LEAVE or EXIT statement is encountered.Fetching a NULL, or even row after row filled with NULLs, does not terminate the loop!