Using &NDB Verbs › Work with NDBs › Retrieve Records from an NDB › Retrieve Keyed Field Statistics (Histogram)

Retrieve Keyed Field Statistics (Histogram)

All keyed fields in an NDB (except the sequence key) contain statistical information on the number of records that have a particular unique key value.

The KEY option of &NDBGET and &NDBSEQ allow retrieval, not of a record, but of a key value and record count. Thus, statistical information can be obtained. This is very fast, as no data is accessed. When reading the NDB in this way, the format specified on &NDBGET is completely ignored. (You can, for example, code FORMAT = NO.) Instead, the data is returned in two NCL variables:

&NDBKEYVALUE keyed field value

&NDBKEYCOUNT keyed field record count

For example, to find out how many records have field surname with a value of 'Smith':

&NDBGET dbname KEY=SURNAME VALUE='SMITH' FORMAT=NO

To get a sequential breakdown of a key field's contents and counts:

&NDBSEQ dbname DEFINE SEQ=S1 KEY=SURNAME
&NDBGET dbname SEQ=S1 FORMAT=NO
&DOWHILE &NDBRC=0
     &WRITE &NDBKEYVALUE &NDBKEYCOUNT
     &NDBGET dbname SEQ=S1 FORMAT=NO
&DOEND