Caché SQL Reference
NULLIF
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

A function that returns NULL if an expression is true.
Synopsis
NULLIF(expression1,expression2)
Arguments
expression1 An SQL expression.
expression2 An SQL expression.
Description
The NULLIF function returns NULL if expression1 is equal to expression2, otherwise it returns expression1. The data type returned in DISPLAY mode or ODBC mode is determined by the data type of expression1.
NULLIF is equivalent to:
SELECT CASE 
WHEN value1 = value2 THEN NULL
ELSE value1
END
FROM MyTable
NULL Handling Functions Compared
The following table shows the various SQL comparison functions. Each function returns one value if the comparison tests True (A equals B) and another value if the comparison tests False (A not equal to B):
SQL Function Comparison Test Return Value
NULLIF expression1 = expression2
True = NULL
False = expression1
IFNULL (2 argument form) expression1 = NULL
True = expression2
False = NULL
COALESCE expression1 = NULL, expression2 = NULL, ...
True = test expression2
False = expression1
ISNULL expression1 = NULL
True = expression2
False = expression1
NVL expression1 = NULL
True = expression2
False = expression1
IFNULL (3 argument form) expression1 = NULL
True = expression2
False = expression3
Examples
The following example uses the NULLIF function to set to null the display field of all records with Age=20:
SELECT Name,Age,NULLIF(Age,20) AS Nulled20
FROM Sample.Person
 
See Also