ISNULL(), NVL()

These two functions let you handle null values that appear in data. ISNULL() tells whether an expression is null, while NVL() lets you substitute for null values.

Usage

lIsItNull = ISNULL( uExpr )

uExpr can be an expression of any type. If its value is .NULL., ISNULL() returns .T.

You need ISNULL() because .NULL. is never equal to any value, even itself. Nor is it unequal to other values. That is,

.NULL.=.NULL.

returns .NULL. and so does:

.NULL.<>.NULL.

The only way to find out if you have a null value is to test with ISNULL(). Because many operators and functions propagate nulls, it’s important to catch them in any situations where they have the potential of propagating through your code, wreaking havoc. As we say in “DBF, FPT, CDX, DBC—Hike!”, the decision to use nulls has some pretty severe consequences for your code.

In VFP 3 and VFP 5, probably the most common place to use ISNULL() was in code like IF TYPE(‘oObject’) = “O” AND NOT IsNull(oObject). This checks first whether a supposed object variable is, in fact, an object—in case, say, you haven’t instantiated it yet. Okay, that makes sense, but why the ISNULL() check? Because when an object is destroyed, the variable referencing it retains its type (“O”), but is assigned the null value. So you need both tests to make sure you have a reference to an object, not just to an object-type variable. Starting in VFP 6, the test is much easier. You can use IF VARTYPE(oObject) = “O”, because VARTYPE() returns “X” for any variable with a null value.

Example

IF NOT ISNULL(dBirthdate)
   * compute age
ENDIF

Usage

uNonNullValue = NVL( uTestExpr, uSubstituteValue )

NVL() provides a way to keep nulls from propagating without having to use ISNULL(). uTestExpr is tested. If it’s not null, its value is returned. If uTestExpr is null, the value of uSubstituteValue is returned.

Think of NVL(x,y) as shorthand for:

IIF(ISNULL(x), y, x)

With a fairly simple text expression, the NVL() version is about 25 percent faster. Presumably, the more complex uTestExpr is, the more time you save with NVL() because it only has to evaluate the expression once, while the IIF() version has to evaluate it twice. As with many such situations, though, unless you’re dealing with an extremely complex expression or performing the computation repeatedly, the times involved are quite small. So, use the more readable version. Fortunately, in this case, we think NVL() is easier to read and less error-prone, too.

Note that built-in functions that are designed to work with groups of records (AVERAGE, SQL SELECT’s aggregate functions, SUM and CALCULATE) usually do not need to use NVL() functions, because these functions, by default, skip .NULL. values in their calculations.

Example

* Say you're totaling the values in an array
* and some of them might be null. If you include them
* in the total, the result will be null. Use NVL() instead.
nTotal = 0
FOR nCnt = 1 TO ALEN(aArray)
   nTotal = nTotal + NVL(aArray[nCnt], 0)
ENDFOR

See Also

Empty(), IsBlank(), Set Null, VarType()