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 ValuesHere 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 NullA 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 ResultA 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.
INSERT SQL and SELECT SQL Process Null Values by Using New ClausesTwo 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 ValuesAn 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
For more information about the .NULL. value and the functions described above, search for topics in the Visual FoxPro Help file.
Article ID: 130307 - Last Review: Jun 20, 2008 - Revision: 1