FirebirdSQL logo
 Procedural SQL (PSQL) StatementsAggregate Functions 

COALESCE Examples

This example picks the Nickname from the Persons table.If it happens to be NULL, it goes on to FirstName.If that too is NULL, “'Mr./Mrs.'” is used.Finally, it adds the family name.All in all, it tries to use the available data to compose a full name that is as informal as possible.This scheme only works if absent nicknames and first names are NULL: if one of them is an empty string, COALESCE will happily return that to the caller.That problem can be fixed by using [fblangref50-scalarfuncs-nullif].

select
  coalesce (Nickname, FirstName, 'Mr./Mrs.') || ' ' || LastName
    as FullName
from Persons
See also

[fblangref50-scalarfuncs-iif], [fblangref50-scalarfuncs-nullif], CASE

DECODE()

Shorthand “simple CASE”-equivalent function

Result type

Depends on input

Syntax
DECODE(<testexpr>,
  <expr1>, <result1>
  [<expr2>, <result2> ...]
  [, <defaultresult>])
Table 1. DECODE Function Parameters
Parameter Description

testexpr

An expression of any compatible type that is compared to the expressions expr1, expr2 …​ exprN

expr1, expr2, …​ exprN

Expressions of any compatible types, to which the testexpr expression is compared

result1, result2, …​ resultN

Returned values of any type

defaultresult

The expression to be returned if none of the conditions is met

DECODE is a shorthand for the so-called “simple CASE” construct, in which a given expression is compared to a number of other expressions until a match is found.The result is determined by the value listed after the matching expression.If no match is found, the default result is returned, if present, otherwise NULL is returned.

The equivalent CASE construct:

CASE <testexpr>
  WHEN <expr1> THEN <result1>
  [WHEN <expr2> THEN <result2> ...]
  [ELSE <defaultresult>]
END
Caution

Matching is done with the ‘=’ operator, so if testexpr is NULL, it won’t match any of the exprs, not even those that are NULL.