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

A function that returns the value of the first expression that is not NULL.
expression A series of expressions to be evaluated. Multiple expressions are specified as a comma-separated list. This expression list has a limit of 140 expressions.
The COALESCE function evaluates a list of expressions in left-to-right order and returns the value of the first non-NULL expression. If all expressions evaluate to NULL, NULL is returned.
Non-numeric expressions (such as strings or dates) must all be of the same data type, and return a value of that data type. Specifying expressions with incompatible data types results in an SQLCODE -378, and a %msg error message value. You can use the CAST function to convert an expression to a compatible data type.
Numeric expressions may be of different data types. If you specify numeric expressions with different data types, the data type returned is the expression data type most compatible with all of the possible result values, the data type with the highest data type precedence. The following data types are compatible and are specified in order of precedence (highest to lowest): DOUBLE, NUMERIC, BIGINT, INTEGER, SMALLINT, TINYINT.
A string is returned unchanged; leading and trailing blanks are retained. A number is returned in canonical form, with leading and trailing zeros removed.
For further details on NULL handling, refer to the NULL and the Empty String section of “Language Elements” in Using Caché SQL.
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
The following Embedded SQL example takes a series of host variable values and returns the first (value d) that is not NULL. Note that the ObjectScript empty string ("") is translated as NULL in Caché SQL:
  SET (a,b,c,e)=""
  SET d="firstdata"
  SET f="nextdata"
  &sql(SELECT COALESCE(:a,:b,:c,:d,:e,:f) INTO :x)
   IF SQLCODE'=0 {
     WRITE !,"Error code ",SQLCODE }
   ELSE {
     WRITE !,"The first non-null value is: ",x }
The following example compares the values of two columns in left-to-right order and returns the value of the first non-NULL column. The FavoriteColors column is NULL for some rows; the Home_State column is never NULL. For COALESCE to compare the two, FavoriteColors must be cast as a string:
SELECT TOP 25 Name,FavoriteColors,Home_State,
COALESCE(CAST(FavoriteColors AS VARCHAR),Home_State) AS CoalesceCol
FROM Sample.Person
The following Dynamic SQL example compares COALESCE to the other NULL-processing functions:
  SET myquery = "SELECT TOP 50 %ID,"_
                "IFNULL(FavoriteColors,'blank') AS Ifn2Col,"_
                "IFNULL(FavoriteColors,'blank','value') AS Ifn3Col,"_
                "COALESCE(CAST(FavoriteColors AS VARCHAR),Home_State) AS CoalesceCol,"_
                "ISNULL(FavoriteColors,'blank') AS IsnullCol,"_
                "NULLIF(FavoriteColors,$LISTBUILD('Orange')) AS NullifCol,"_
                "NVL(FavoriteColors,'blank') AS NvlCol"_
                " 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