NULLIF scalar function

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

The schema is SYSIBM.

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. For more information on compatibility, refer to the compatibility matrix in Table 1.

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.

Notes

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)