- Overview

Null data is a relational database concept used to describe data items whose values are unknown, or do not exist. In ‘flat files’ these items are often represented by zeros or blanks, however such descriptions may not be truly accurate.

For example, a customer (for whatever reason) does not have a telephone (non-existent). It would not be correct to list his telephone number as ‘0’ on the Customer Master file. In another example a retailer has ordered a new item, but has not yet determined its selling price (unknown value). The Inventory Master should certainly not list the retail price as ‘$0.00’. While this may please the customers, it would not make for very favourable profit projections.

Relational database technology provides us with the concept of null data to distinguish between values of ‘zero’ and truly null values, as described above. The PROIV interface to relational database systems includes sensitivity to null data values. Columns that contain null values are flagged by ‘null data indicators’ similar to those maintained by relational database systems.

Null values introduce certain anomalies into data and file processing. For example, according to some standards for SQL, null columns are considered to be duplicates. However, comparisons of null columns never produce an ‘equal’ result. In addition, any arithmetic or alphanumeric operation that contains null data will always result in null data. Furthermore, there are other published SQL standards that do not always coincide with these standards. Such as, if you attempt to write a null date key field on an SQL database, then the default date field will be set to 01/01/1800. The best default is to enter SETNULL, which sets data buffers to a null data state.

The concept of null data should always remain transparent to the end user. All null value determination, recognition, and analysis should be resolved by the application developer using PROIV logic.

PROIV has a field attribute, null-permissive, that allows you to read and write null values to relational database systems that support them.

The following logic keywords manipulate null data:

ISNULL allows the function to test for a null data condition in data buffers.

SETNULL sets data buffers to a null data state.

Comment on this topic

Topic ID: 720154