Using &NDB Verbs › Work with NDBs › Retrieve Records from an NDB › Define Fields to Return (&NDBFMT)

Define Fields to Return (&NDBFMT)

When retrieving records from an NDB, it is likely that not all the fields in a record are needed. Also, it might be desirable to return the fields in NCL variables with different names. The &NDBFMT verb lets you define any number of named formats, that can be nominated on an &NDBGET statement, referring to the same NDB. The nominated format determines which fields will be returned, and the names of the NCL variables that will receive the values.

The defined format can request that all fields defined in the database be returned (ALL-FIELDS), or that all fields defined as keyed be returned (KEY-FIELDS), or that no fields be returned (NO-FIELDS) (useful for just checking for existence of a record), or that a list of nominated fields be returned, optionally in NCL variables with a different name.

Note: A format can also be specified directly on the &NDBGET statement, but this is not recommended, particularly if the &NDBGET statement is in a loop. The format definition must be parsed and encoded every time the &NDBGET statement is executed.

A format stays defined until explicitly deleted (by &NDBFMT ... DELETE), or until an &NDBCLOSE is executed for that NDB.

The following are examples of predefined formats:

&NDBFMT MYNDB DEFINE FORMAT=FMT1 DATA ALL-FIELDS
&NDBFMT MYNDB DEFINE FORMAT=FMT2 DATA NO-FIELDS
&NDBFMT MYNDB DEFINE FORMAT=FMT3 START
&NDBFMT MYNDB DATA FIELDS
&NDBFMT MYNDB DATA SURNAME FIRSTNAME
&NDBFMT MYNDB DATA ( LNAME = LASTNAME)
&NDBFMT MYNDB END

It is evident that the START/DATA/END syntax, as described under &NDBADD and &NDBUPD, is also available when using &NDBFMT. Thus, complex variables and open-ended formats can be defined. The START/DATA/END syntax is not available on the &NDBGET verb. Any format defined in an &NDBGET statement must fit on the &NDBGET statement itself.

The examples all assume predefined formats.