Skip to main content
Previous sectionNext section

ISNULL

A function that tests for NULL and returns the appropriate expression.

Synopsis

ISNULL(check-expression,replace-expression)

Arguments

Argument Description
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:

  • If check-expression is NULL, replace-expression is returned.

  • If check-expression is not NULL, check-expression is returned.

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 logical comparison tests True (A same as B) and another value if the logical comparison tests False (A not same as B). These functions allow you to perform NULL logical comparisons. You cannot specify NULL in an actual equality (or non-equality) condition comparison.

SQL Function Comparison Test Return Value
ISNULL(ex1,ex2) ex1 = NULL
True returns ex2
False returns ex1
IFNULL(ex1,ex2) [two-argument form] ex1 = NULL
True returns ex2
False returns NULL
IFNULL(ex1,ex2,ex3) [three-argument form] ex1 = NULL
True returns ex2
False returns ex3
{fn IFNULL(ex1,ex2)} ex1 = NULL
True returns ex2
False returns ex1
NVL(ex1,ex2) ex1 = NULL
True returns ex2
False returns ex1
NULLIF(ex1,ex2) ex1 = ex2
True returns NULL
False returns ex1
COALESCE(ex1,ex2,...) ex = NULL for each argument
True tests next ex argument. If all ex arguments are True (NULL), returns NULL.
False returns ex

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
Copy code to clipboard

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"
Copy code to clipboard

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"
Copy code to clipboard

See Also