NULLIF

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

NULLIF(expression-1,expression-2)
The arguments must be compatible data types.
expression-1
An expression that returns a value of any built-in data type other than a DATALINK or XML or any distinct data type other than a distinct type that is based on a DATALINK or XML.
expression-2
An expression that returns a value of any built-in data type other than a DATALINK or XML or any distinct data type other than a distinct type that is based on a DATALINK or XML .

The attributes of the result are the attributes of the first argument. The result can be null. The result is null if the first argument is null or if both arguments are equal.

The result of using NULLIF(e1,e2) is the same as using the expression 
  CASE WHEN e1=e2 THEN NULL ELSE e1 END

Note that 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 operand, e1.

Example

  • Assume host variables PROFIT, CASH, and LOSSES have DECIMAL data types with the values 4500.00, 500.00, and 5000.00 respectively: 
      SELECT NULLIF (:PROFIT + :CASH, :LOSSES )
    FROM SYSIBM.SYSDUMMY1
    Returns the null value.