NDB Concepts › Null Values and Null Fields

Null Values and Null Fields

One of the most powerful features of NDBs is the use of null fields, an understanding of which is essential to the effective use of an NDB.

An NDB is a field-oriented database. Data is always accessed by field name. Other databases may have the concept of a record or group, being a collection of fields, that can be accessed by the name of the record or group. An NDB record is the actual, complete record, as logically accessed by RID.

At first, then, it might seem that an NDB can contain only one record-type, where record-type corresponds to a supplier, an order, or a customer record, for example. This is not the case. In fact, an NDB can contain almost any number of logical record types, each of which can be accessed separately. To achieve this, an NDB uses the concept of the null field. A null field is simply a defined field that is not present in a record.

The number of defined fields in the control record includes the number of null fields and each null field has a field definition record. However, the field is not physically present in the record. This is clearly not the same as a field that is present, but contains a null value (for example, blank).

Even if the field is defined as keyed, a null field is never keyed. Thus, any attempt to access by using keys will never retrieve a record that has that field null.

For any data format, a field can have one of the following logical values:

• Not present (that is, null field).
• Present, but null-valued. For a character field, this is defined as all blank. For a numeric or floating point field, this is defined as 0, for a hexadecimal field, this is defined as all blank (stored as present with a length of 0), and for a date field, this is defined as YYMMDD = 000000 (the only invalid date value allowed).
• Present, with a value other than the null value described previously.

In NCL terms, the following statements illustrate the three states of a field:

&VALUE =

Sets &VALUE to not-present (the actual variable is deleted from the NCL procedure's variable pool).

&VALUE = &SETBLNK 1

Sets &VALUE to present, with the value of one blank.

&VALUE = value

Sets &VALUE to present, with the value of value.