NULLIF

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


>>-NULLIF(expression,expression)-------------------------------><

The schema is SYSIBM.

Start of change The two arguments must be compatible. The arguments can be of either a built-in or user-defined data 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 matrices in Assignment and comparison. End of change

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

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

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.

Example: 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)