NULLIF

Edit online

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

The two arguments must be compatible. The arguments can be of either a built-in or user-defined distinct type. Neither argument can be a BLOB, CLOB, DBCLOB, or XML. Character-string and graphic-string arguments are compatible with datetime values.

If there are any mixed character string or graphic string and numeric arguments, the string value is implicitly cast to a DECFLOAT(34) value.

The attributes of the result are the attributes of the first argument.

Note:

Syntax alternatives:

The result of using NULLIF(e1,e2) is the same as using the CASE 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 the evaluation not true. In this case, NULLIF returns the value of the first argument.

Examples

Example 1:

Assume that host variables PROFIT, CASH, and LOSSES have decimal data types with the values of 4500.00, 500.00, and 5000.00 respectively. The following function returns a null value:

   NULLIF (:PROFIT + :CASH , :LOSSES)