SQL Reference

NULLIF

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

The schema is SYSIBM.

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

The arguments must be comparable (see Assignments and Comparisons). They can be of either a built-in (other than a long string or DATALINK) or distinct data type (other than based on a long string or DATALINK). ⁴⁵ 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 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 argument.

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.

⁴⁵

This function may not be used as a source function when creating a user-defined function. Since it accepts any compatible data types as arguments, it is not necessary to create additional signatures to support user-defined distinct types.