GEN_ID
Example
new.rec_id = gen_id(gen_recnum, 1);
Functions for Sequences (Generators)
GEN_ID
Examplenew.rec_id = gen_id(gen_recnum, 1);
COALESCE()
Returns the first non-NULL
argument
Depends on input
COALESCE (<exp1>, <exp2> [, <expN> ... ])
Parameter | Description |
---|---|
exp1, exp2 … expN |
A list of expressions of compatible types |
The COALESCE
function takes two or more arguments and returns the value of the first non-NULL
argument.If all the arguments evaluate to NULL
, the result is NULL
.
COALESCE
ExamplesThis 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