NDB Concepts › Null Values and Null Fields › Rules for Null Fields

Rules for Null Fields

There are several rules regarding null fields:

• A null field is never keyed. Thus, access using keys for that field name will never retrieve a record with that field a null field.
• A null field never matches anything, not even another null field. For example, none of the following &NDBSCAN statements will retrieve the entire database, if field NAME is null in some records.

  &NDBSCAN dbname     FIELD NAME EQ VALUE 'FRED' OR +
  

                      FIELD NAME NE VALUE 'FRED'
  &NDBSCAN dbname     FIELD NAME EQ FIELD NAME
  

  In the second example, comparing a field to itself in a record, it might seem that some records should be selected. The rule about null fields is still honored in this case.

• You cannot retrieve a record with null fields. Retrieving a record with null fields causes the NCL variables receiving the requested null fields to be deleted (that is, set to null).
• A null field can be indicated on an &NDBADD statement by omitting the fieldname = fieldvalue clause for that field, or, on an &NDBADD or an &NDBUPD statement, by using a currently undefined (that is, null) NCL variable as the fieldvalue, for example, FIELDX = &NULL, or by using the syntax FIELDX NULL.
• The only way that records containing a particular null field can be selected in an &NDBSCAN is to use the PRESENT and ABSENT or IS [NOT] NULL operators. These operators allow records to be selected that contain the nominated field (PRESENT) or records that do not contain the nominated field (ABSENT).

Using null fields, the previous example of a database containing supplier, order, and customer records can be built by inserting only supplier fields for supplier records, order fields for order records, and customer fields for customer records. The records are now disjoint. A retrieval by CUSTNO, for example, would only retrieve records containing the CUSTNO field, and so on for supplier and order.

When defining fields in an NDB, a field can be made mandatory by specifying NULLFIELD=NO.