NVL
Synopsis
NVL(check-expression,replace-expression)
Arguments
| Argument | Description |
|---|---|
| check-expression | The expression to be evaluated. |
| replace-expression | The expression that is returned if check-expression is NULL. |
Description
NVL 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 arguments check-expression and replace-expression can have any data type. If their data types are different, Caché converts replace-expression to the data type of check-expression before comparing them. The data type of the return value is always the same as the data type of check-expression, unless check-expression is character data, in which case the return value’s data type is VARCHAR2.
Note that NVL is supported for Oracle compatibility, and is the same as the ISNULL function.
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 |
|---|---|---|
| NVL(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 |
| ISNULL(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
This following example returns the replace-expression (99) because the check-expression is NULL:
SELECT NVL(NULL,99) AS NullTestThis following example returns the check-expression (33) because check-expression is not NULL:
SELECT NVL(33,99) AS NullTestThe 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)="NVL(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"
