FirebirdSQL logo

Firebird 1.0: the *NVL functions

Firebird 1.0 doesn’t have COALESCE.However, you can use four UDFs that provide a good part of its functionality.These UDFs reside in the fbudf lib and they are:

  • iNVL, for integer arguments

  • i64NVL, for bigint arguments

  • dNVL, for double precision arguments

  • sNVL, for strings

The *NVL functions take exactly two arguments.Like COALESCE, they return the first argument if it’s not NULL;otherwise, they return the second.Please note that the Firebird 1.0 fbudf lib — and therefore, the *NVL function set — is only available for Windows.

Converting values to NULL

Sometimes you want certain values to show up as NULL in the output (or intermediate output).This doesn’t happen often, but it may for instance be useful if you want to exclude certain values from summing or averaging.The NULLIF functions can do this for you, though only for one value at the time.

Firebird 1.5 and up: the NULLIF function

The NULLIF internal function takes two arguments.If their values are equal, the function returns NULL.Otherwise, it returns the value of the first argument.

A typical use is e.g.

select avg( nullif( Weight, -1 ) ) from FatPeople

which will give you the average weight of the FatPeople population, without counting those with weight -1.(Remember that aggregate functions like AVG exclude all NULL fields from the computation.)

Elaborating on this example, suppose that until now you have used the value -1 to indicate “weight unknown” because you weren’t comfortable with NULLs.After reading this guide, you may feel brave enough to give the command:

update FatPeople set Weight = nullif( Weight, -1 )

Now unknown weights will really be unknown.

Firebird 1.0: the *nullif UDFs

Firebird 1.0.x doesn’t have the NULLIF internal function.Instead, it has four user-defined functions in the fbudf lib that serve the same purpose:

  • inullif, for integer arguments

  • i64nullif, for bigint arguments

  • dnullif, for double precision arguments

  • snullif, for strings

Please note that the Firebird 1.0 fbudf lib — and therefore, the *nullif function set — is only available for Windows.

Warning

The Firebird 1 Release Notes state that, because of an engine limitation, these UDFs return a zero-equivalent if the arguments are equal.This is incorrect: if the arguments have the same value, the functions all return a true NULL.

However — they also return NULL if the first argument is a real value and the second argument is NULL.This is a wrong result.The Firebird 1.5 internal NULLIF function correctly returns the first argument in such a case.