Use null values in Visual FoxPro
This article describes how to use null data values in Visual FoxPro commands and functions.
Original product version: Visual FoxPro
Original KB number: 130307
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.
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 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.
EMPTY()each return false (.F.) when passed a null value.
ISNULL()returns true (.T.) when passed a null value.
SELECTSQL process null values through the IS NULL and IS NOT NULL clauses.
SQL Aggregate functions, such as
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.
The remainder of this article gives more information and examples for these general rules.
Commands generate an error when passed a null
A Visual FoxPro command is a statement that results in an action. Examples of commands are
DELETE. For example, the
USE command returns an error for this code:
STORE .NULL. TO nWorkArea USE mytable IN (nWorkArea)
The IN clause of the
USE command is expecting a numeric or alpha value, when passed a
.NULL. the error Table Number is Invalid is generated.
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)
This would open mytable in the first available workarea. See the Help file or Visual FoxPro documentation for more information about the
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
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
cLastName = "Johnson" nBegin = 5 nExtract = .NULL. ?SUBSTR(cLastName,nBegin,nExtract)
The exceptions to this rule are the
EMPTY() functions - each of which return a
.F. value. The
ISNULL() function returns a
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
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
To locate null values, use the IS NULL clause.
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
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
SELECT MAX(nYTDSales) from mytable
Any SQL aggregate function performed on a field that contains
.NULL. values ignore the
.NULL. values, returning a result that treats the null values as if they do not exist (are not part of the aggregate).
For more information about the
.NULL. value and the functions described above, search for topics in the Visual FoxPro Help file.