How to Use Null Values in Visual FoxPro

• Microsoft Visual FoxPro for Windows, version 3.0

SUMMARY

Microsoft Visual FoxPro version 3.0 supports null data values. Versions of FoxPro prior to version 3.0 did not directly support null data values. This article describes the general rules Visual FoxPro follows for handling null values (represented as .NULL.) when they passed to Visual FoxPro commands or functions.

MORE INFORMATION

Use a null value (.NULL.) value when a value is missing, irrelevant, or unknown. In previous versions of FoxPro, values that were unknown or missing were usually represented by spaces or zeroes, which could have been misinterpreted. With Visual FoxPro, you can now store a null value in a field.

It is important to note that a null value (.NULL.) in Visual FoxPro is not the same as an empty, blank, or zero value. Null represents the absence of a value, so null is never equal to, greater than, or less than another value, null or non-null. Visual FoxPro support for null values complies with the ANSI standards and affects any area of the product where values and expressions are used.

General Rules for Null Values

Here are the general rules for null values passed to Visual FoxPro commands or functions:

• Commands generate an error when passed a null.
• Functions that accept null values return .NULL. as a result.
• Functions expecting a numeric value will generate an error if supplied with a null.
• ISBLANK(), ISDIGIT(), ISLOWER(), ISUPPER(), ISALPHA(), and EMPTY() each return false (.F.) when passed a null value.
• ISNULL() returns true (.T.) when passed a null value.
• The commands INSERT SQL and SELECT SQL process null values through the IS NULL and IS NOT NULL clauses.
• SQL Aggregate functions, such as MAX(), MIN(), and SUM() ignore all null values in the aggregate.
• Visual FoxPro aggregate functions propagate .NULL. if all supplied values are null values, otherwise, any null value is ignored.

Commands Generate an Error When Passed a Null

A Visual FoxPro command is a statement that results in an action. Examples of commands are USE, BROWSE, and DELETE. For example, the USE command returns an error for this code:

   STORE .NULL. TO nWorkArea
   USE mytable IN (nWorkArea)

The NVL() function may be used to remove null values from calculations or operations where null values are not supported or are not relevant.

   STORE .NULL. TO nWorkArea
   USE mytable IN NVL(nWorkArea,0)

Functions that Accept Null Values Return .NULL. as a Result

A Visual FoxPro function is a routine that performs a specific task and takes zero or more arguments. Examples of functions include ISBLANK(), UPPER(), and SUBSTR(). Most Visual FoxPro functions allow a null value to be passed as an argument without generating an error, however a .NULL. is returned from the function. In other words, when you pass a null value to a function, the result is always null. This is also how null values are treated in mathematical equations. For example a null value added to 500 equals null, and a null value multiplied by zero equals null (not zero).

The following example code returns .NULL.:

   cLastName = "Johnson"
   nBegin = 5
   nExtract = .NULL.
   ?SUBSTR(cLastName,nBegin,nExtract)

INSERT SQL and SELECT SQL Process Null Values by Using New Clauses

Two new clauses (IS NULL and IS NOT NULL) handle nulls in the INSERT and SELECT SQL commands. For example, to locate all records in a table where cLastName is not null, use this command:

   SELECT cLastName FROM mytable WHERE cLastName IS NOT NULL

SQL Aggregate Functions Ignore Null Values

An aggregate function is a function that performs a numeric operation such as addition, minimum, maximum, or average on a group (aggregate) of values. Examples of aggregate functions include MAX(), MIN(), and SUM().

The SELECT SQL command, for example, can use aggregate functions to retrieve numeric values from tables. For example, the following SELECT command returns the maximum value from a field named nYTDSales:

   SELECT MAX(nYTDSales) from mytable

Visual FoxPro Aggregate Functions and Null Values

Unlike SQL aggregate functions, Visual FoxPro aggregate functions such as MAX(), and MIN() return .NULL. if all supplied values are null values, otherwise, any null value is ignored. For example the result of the following MAX() function is 10:

   ? MAX(2,.NULL.,10)

   x=.NULL.
   y=.Null.
   z=.NULL.
   ? MAX(x,y,z)