NULLIF scalar function

The NULLIF function returns a null value if the arguments are equal, otherwise it returns the value of the first argument.

The schema is SYSIBM.

expression1

An expression that returns a value of any built-in or user-defined data type.

expression2

An expression that returns a value of any built-in or user-defined data type that is comparable with the data type of the other argument according to the rules for equality comparison.

The result of using NULLIF(e1,e2) is the same as using the following expression:

   CASE WHEN e1=e2 THEN NULL ELSE e1 END

When e1=e2 evaluates to unknown (because one or both arguments is NULL), CASE expressions consider this not true. Therefore, in this situation, NULLIF returns the value of the first argument.

Notes

The NULLIF function cannot be used as a source function when creating a user-defined function. Because this function accepts any comparable data types as arguments, it is not necessary to create additional signatures to support user-defined data types.

Example

Assume host variables PROFIT, CASH, and LOSSES have DECIMAL data types with the values 4500.00, 500.00, and 5000.00 respectively:

   NULLIF (:PROFIT + :CASH , :LOSSES )

Returns a null value.