Using &NDB Verbs › Notes on Sequential Retrieval › Read by Sparse Keys

Read by Sparse Keys

When defining a sequence on a key field that is defined (or defaulted) with NULLFIELD=YES, it is possible that some records do not contain the field. These records will not be returned when reading by that sequence. This is because no index record is built for fields not present in a data record. This can be especially useful when the database contains multiple record types, as illustrated in one of the following examples.

Example 1: Read by Sparse Keys

This example shows how two sequences defined on a single database, where the logical record types are disjoint, using different key fields, can perform a master/transaction update.

&NDBSEQ MYNDB DEFINE SEQ=MAST FIELD=MASTKEY
&NDBSEQ MYNDB DEFINE SEQ=TRAN FIELD=TRANKEY
&GOSUB .READMAST  -* read mast, set &MASTKEY to 999999 if
                  -* eof
&GOSUB .READTRAN  -* read tran, set &TRANKEY to 999999 if
                  -* eof
&DOWHILE &MASTKEY.&TRANKEY NE 999999.999999
   &IF &MASTKEY = &TRANKEY &DO
            ...process match
      &GOSUB .READTRAN
   &DOEND
   &ELSE &IF &MASTKEY GT &TRANKEY &DO
            ...process unmatched transaction
      &GOSUB .READTRAN
   &DOEND
   &ELSE &DO
            ...process unmatched master
      &GOSUB .READMAST
   &DOEND
&DOEND

Example 2: Read by Sparse Keys

This example shows how to use SKIP= to extract a subset of a database, perhaps for statistical analysis.

&NDBSEQ  MYNDB DEFINE SEQ=S1 RID
&NDBINFO MYNDB DB    -* obtain # records in DB
&SKIP = &NDBDBNRECS / 1000 -* determine skip to get 1000
                     -* recs
&NDBGET  MYNDB SEQ=S1 SKIP=&SKIP FORMAT ALL-FIELDS
&DOWHILE &NDBRC = 0
               ...write sampled record.
   &NDBGET MYNDB SEQ=S1 SKIP=&SKIP FORMAT ALL-FIELDS
&DOEND

Example 3: Read by Sparse Keys

This example shows how to process a selection list using a sequence and SKIP/DIR.

&NDBSEQ MYNDB DEFINE FIELD=SURNAME KEEP=YES
&SKIPVAL = 1      -* initial skip
.LOOP &GOSUB .BUILD_PANEL  -* builds panel.
                  -* position now is last record on screen
&PANEL XYZPANEL
&IF &INKEY = PF08 &THEN &DO
   &SKIPVAL = +1  -* skip to next record after bottom
   &GOTO .LOOP
&DOEND
&ELSE       &IF &INKEY = PF07 &DO
&SKIPVAL = (0-(&LUROWS*2))
   -* skip top + back 1
-*screens length
   &GOTO .LOOP
&DOEND