&NDBGET

Retrieves a record from an NDB database. Histograms (statistical information) is retrieved for keyed fields and histogram sequences.

&NDBGET dbname { RID=n [ OPT= { KEQ | KGE | KGT | KLE | KLT } ] |
                 KEY=fieldname VALUE=value
                  [ OPT= {KEQ | KGE | KGT | KLE | KLT } ] |
                 FIELD=fieldname VALUE=value
                  [ OPT= { KEQ | KGE | KGT | KLE | KLT | GEN } ] |
                 SEQUENCE=seqname [ SKIP=n ]
                  [ DIR= { FWD | BWD } ] } 
               [ MODFLD= { NO | YES } ]
               { FORMAT=formatname [ FSCOPE={ PROCESS | GLOBAL } ] |
                 FORMAT format-text }

The &NDBGET verb allows an NCL procedure to retrieve a record from an NDB. There are four basic retrieval methods:

Get by record ID (RID). This method is useful when the procedure knows the RID of the desired record.
Get by key-field value. This method allows a procedure to get a record that matches a particular keyed field value.
Get a keyed field value and record count.
Get from a predefined sequence, defined by &NDBSEQ or &NDBSCAN. This method allows sequential retrieval to be easily performed.

Operands:

dbname

This operand is mandatory. It specifies the name of the NDB that you want to retrieve the record from. The NDB specified must have been previously opened by an &NDBOPEN statement.

RID=n

Indicates a get by record ID (RID), and provides the RID of the desired record, or the comparison RID if not using the default OPT=KEQ.

OPT={ KEQ | KGE | KGT | KLE | KLT }

This optional operand indicates the relation to the passed RID that the retrieved record RID will have.

KEQ: Retrieves the record with the passed RID, if it exists.
KGE: Retrieves the record with the passed RID, if it exists. If none exists, the nearest RID greater than the passed RID is retrieved, if one exists.
KGT: Retrieves the record with the nearest RID greater than the passed RID, if one exists.
KLE: Retrieves the record with the passed RID, if it exists. If none exists, the nearest RID less than the passed RID is retrieved, if one exists.
KLT: Retrieves the record with the nearest RID less than the passed RID, if one exists.

KEY=fieldname VALUE=value

Indicates that a keyed field histogram is to be performed. fieldname must be keyed, and not a sequence key. Rather than return the associated record (as GET FIELD= does), instead the field value (as requested by the VALUE operand) is retrieved, and the count of records having that value is also returned.

The returned key field value is always returned in &NDBKEYVALUE. The returned record count is always returned in &NDBKEYCOUNT. The &NDBRID system variable is always set to 0.

The VALUE= operand provides the key value for a get by keyed field. The value is used to locate a record for the named field. Value must be a valid value for the type of the field. For example, for a numeric field, it must be a valid number. If the value contains special characters or embedded blanks, it must be enclosed in quotes.

Note: Embedded blanks in NCL variable values are a special case, and are treated as part of the value.

OPT= { KEQ | KGE | KGT | KLE | KLT | GEN }

Indicates the relation to the passed VALUE that the retrieved record key FIELD will have.

KEQ

Retrieves the record with the passed VALUE, if it exists.

KGE

Retrieves the record with the passed VALUE, if it exists. If none exists, the nearest VALUE greater than the passed VALUE is retrieved, if one exists.

KGT

Retrieves the record with the nearest VALUE greater than the passed VALUE, if one exists.

KLE

Retrieves the record with the passed VALUE, if it exists. If none exists, the nearest VALUE less than the passed VALUE is retrieved, if one exists.

KLT

Retrieves the record with the nearest VALUE less than the passed VALUE, if one exists.

GEN

(Character fields only) Retrieves the record with the lowest value generically equal to the passed VALUE, if one exists.

Note: Trailing blanks are significant in the passed value in this case.

FIELD=fieldname

Indicates a get by a keyed field, and provides the name of that field. If this operand is specified, the VALUE=value operand must also be coded. The named field must be a defined field on the database, and it must be keyed.

VALUE=value

Provides the key value for a get by keyed field. The value is used to locate a record with a value satisfying the OPT= relation for the named field. Value must be a valid value for the type of the field, for example, it must be a valid number for a numeric field. If the value contains special characters or embedded blanks, it must be enclosed in quotes.

Note: Embedded blanks in NCL variable values are a special case, and are treated as part of the value.

OPT= { KEQ | KGE | KGT | KLE | KLT | GEN }

Indicates the relation to the passed VALUE that the retrieved record key FIELD will have.

KEQ

Retrieves the record with the passed VALUE, if it exists.

KGE

Retrieves the record with the passed VALUE, if it exists. If none exists, the nearest VALUE greater than the passed VALUE is retrieved, if one exists.

KGT

Retrieves the record with the nearest VALUE greater than the passed VALUE, if one exists.

KLE

Retrieves the record with the passed VALUE, if it exists. If none exists, the nearest VALUE less than the passed VALUE is retrieved, if one exists.

KLT

Retrieves the record with the nearest VALUE less than the passed VALUE, if one exists.

GEN

(Character fields only) Retrieves the record with the lowest value generically equal to the passed VALUE, if one exists.

Note: Trailing blanks are significant in the passed value, in this case.

SEQUENCE=seqname

Indicates a get from a predefined sequence, as defined by an &NDBSEQ or &NDBSCAN statement. seqname is the name of the sequence to retrieve from. If the SKIP= and DIR= operands are not specified, they default to SKIP=+1 and DIR=FWD, thus giving standard, forward, sequential retrieval.

A key field histogram sequence is specified.

The returned key field value is always returned in &NDBKEYVALUE. The returned record count is always returned in &NDBKEYCOUNT. The &NDBRID system variable is always set to 0.

As for a direct get on a keyed field, the format is ignored and the returned fields are as described previously.

SKIP=n

Allows specification of a skip amount, that is, the number of records to skip over before retrieving one. SKIP=+1 is the default, causing a skip to the next record in the direction indicated by DIR. SKIP=0 will cause a reread of the last record obtained from this sequence. A negative skip amount reverses the direction specified by DIR.

Note: This is perfectly symmetrical; SKIP=-1, DIR=FWD is equivalent to SKIP=+1, DIR=BWD, and so on.

DIR= { FWD | BWD }

Allows specification of the direction of sequential retrieval. FWD means ascending values, BWD means descending values (assuming a positive skip amount).

FORMAT=fmtname [ FSCOPE={ PROCESS | GLOBAL } ]

FORMAT=fmtname specifies that the input format fmtname, defined on the &NDBFMT statement, is to be used.

The nominated format must exist in the nominated scope. PROCESS is the default and means a format defined by the current NCL process. GLOBAL indicates a format is to be found in the global format pool for the NDB.

If this operand is specified, the START, DATA, CANCEL, and END operands are not allowed.

MODFLD = { YES | NO }

If MODFLD=YES is specified, any NCL variables which previously had the modified field attribute set will have that attribute reset, provided the GET operation produces a zero return code. In addition, any fields which were modified as a result of the GET statement will have their modified attribute set. This includes variables set to pad characters as a result of the NULLFIELD option of the &NDBFMT.

The system variable &ZMODFLD is used to return the names of the modified fields and &ZVARCNT will be available as the number of modified fields.

This option is used with the NORETURN option (see the description of the &NDBFMT verb) for improved efficiency. If MODFLD=NO is specified or defaulted, no modified field attribute reset will occur, and the variables will not have their modified attributes set. The system variables &ZMODFLD and &ZVARCNT will be unchanged.

Examples: &NDBGET

The following example retrieves the record with the RID in &SAVERID, and returns all defined database fields to the procedure (assuming format F1 as defined in the &NDBFMT examples). If there is no record with that RID, &NDBRC will be set to 1, and no fields will be returned.

&NDBGET MYNDB RID=&SAVERID FORMAT=F1

The next example shows one way to sequentially read an entire database, in RID sequence. The first &NDBGET gets the record with the lowest RID on the database, and the second &NDBGET gets the next-highest, until the last record is read. There must not be any &NDBxxx statements in the process record section, as &NDBRID would lose its value. In this case, the value must be saved in a user variable. (See the next example for a better approach.)

&NDBGET MYNDB RID=0 OPT=KGE FORMAT ALL-FIELDS

&DOWHILE &NDBRC = 0
   ... process record
   &NDBGET MYNDB RID=&NDBRID OPT=KGT FORMAT ALL-FIELDS 
&DOEND

The next example uses a defined sequence to read the entire database, but only returning every fifth record (this is useful for creating test files).

&NDBSEQ MYNDB DEFINE SEQUENCE=S1 RID
&NDBGET MYNDB SEQUENCE=S1 SKIP=5 FORMAT ALL-FIELDS
&DOWHILE &NDBRC = 0
   ... process record
   &NDBGET MYNDB SEQ=S1 SKIP=5 FORMAT ALL-FIELDS
&DOEND

The next example sequentially reads MYNDB forwards, backwards, and then forwards, and so on, forever. The sequence is defined with KEEP=YES, which means that it stays defined at each EOF. Whenever an EOF is reached, the skip is inverted (+1 - -1, -1 - +1), and the get loop restarted.

-* database shuttlecock. 
&SKIP = +1 &NDBSEQ MYNDB DEFINE SEQ=S2 FIELD=SURNAME KEEP=YES
&DOWHILE A = A
   &NDBGET MYNDB SEQ=S2 SKIP=&SKIP FORMAT ALL-FIELDS
   &DOWHILE &NDBRC=0      ... process record
      &NDBGET MYNDB SEQ=S2 SKIP=&SKIP FORMAT ALL-FIELDS
   &DOEND
   &SKIP = 0 - &SKIP
&DOEND

Notes:

Errors encountered whilst processing the &NDBGET statement may cause the procedure to terminate, or may just be reflected in the &NDBRC system variable, depending on the setting of &NDBCTL ERROR option.

A record-not found condition on RID and FIELD gets will set &NDBRC to 1. An end-of-file condition on SEQ gets will set &NDBRC to 2.

The actual NCL variables set by a successful &NDBGET depend on the format used. An unsuccessful get never alters any NCL variables. Thus, when an end-of-file response is returned, the NCL variables retain the values set by the last successful &NDBGET.

The &NDBGET statement may specify a format description itself, but the START/DATA/END option is not available. Therefore, the format description must fit onto a single statement. It is more efficient to pre-define formats that are used more than once in a procedure.

When reading via a sequence, an EOF condition (&NDBRC = 2) will delete the sequence, unless it was defined with the KEEP=YES option (on &NDBSEQ or &NDBSCAN). If MODFLD=YES is specified, the order in which &ZMODFLD returns field names is undefined.

More information: