Caché SQL Reference
ISNULL
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

A function that tests for NULL and returns the appropriate expression.
Synopsis
ISNULL(check-expression,replace-expression)
Arguments
check-expression The expression to be evaluated.
replace-expression An expression that is returned if check-expression is NULL.
Description
ISNULL evaluates check-expression and returns one of two values:
The possible data type(s) of replace-expression must be compatible with the data type of check-expression. The data type returned in DISPLAY mode or ODBC mode is determined by the data type of check-expression.
Note that the ISNULL function is the same as the NVL function, which is provided for Oracle compatibility.
Refer to NULL section of the “Language Elements” chapter of Using Caché SQL for further details on NULL handling.
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
In the following example, the first ISNULL returns the second expression (99) because the first expression is NULL. The second ISNULL returns the first expression (33) because the first expression is not NULL:
SELECT ISNULL(NULL,99) AS IsNullT,ISNULL(33,99) AS IsNullF
 
The following Dynamic SQL example returns the string 'No Preference' if FavoriteColors is NULL; otherwise, it returns the value of FavoriteColors:
  ZNSPACE "SAMPLES"
  SET myquery=3
    SET myquery(1)="SELECT Name,"
    SET myquery(2)="ISNULL(FavoriteColors,'No Preference') AS ColorChoice "
    SET myquery(3)="FROM Sample.Person"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of data"
 
Compare the behavior of ISNULL with IFNULL:
  ZNSPACE "SAMPLES"
  SET myquery=3
    SET myquery(1)="SELECT Name,"
    SET myquery(2)="IFNULL(FavoriteColors,'No Preference') AS ColorChoice "
    SET myquery(3)="FROM Sample.Person"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of data"
 
See Also