Verbs and Built-in Functions › &NDBSEQ

&NDBSEQ

Defines, deletes, or resets a sequential retrieval path for a NetMaster database (NDB). Histograms (statistical information) is retrieved for keyed fields.

&NDBSEQ dbname { DEFINE SEQUENCE=seqname { RID | FIELD=fieldname | KEY=fieldname }
                [ FROM=value ] [ TO=value ]
                [ VALUE=value ] [ GENERIC=value ]
                [ KEEP= { NO | YES } ] |
                   DELETE SEQUENCE= { seqname | * } |
                   RESET SEQUENCE=seqname 
                [ REPOS=value | RID=n | RELPOS=n ] }

The &NDBSEQ statement allows an NCL procedure to define, delete, or reset a sequential access path to an NDB.

A sequential path is defined to be by RID, or by any key field defined in the database. The bounds of the path can also be defined.

An NCL procedure can have any number of currently defined sequences. Positioning is maintained independently for each.

The &NDBSCAN statement can also define a sequence, as the result from a scan. The &NDBSEQ statement is used to delete or reset an &NDBSCAN-defined sequence.

Operands:

dbname

Specifies the name of the NDB that you wish to define, delete, or reset a sequence in. This operand is mandatory. The NDB named must have been previously opened by an &NDBOPEN statement.

DEFINE

This operand indicates a new sequence is being defined. SEQUENCE=seqname must be coded.

SEQUENCE=seqname

This operand, for DEFINE, names the new sequence, and, for RESET, names an existing sequence that is to be reset. seqname is a 1- to 12-character name, the first being alphabetic or national, the remainder alphanumeric or national. Sequence names are unique to an NCL procedure. For DEFINE, seqname must not currently exist, including any scan result lists.

RID

This operand indicates that the sequence being defined is to be by record ID (RID). This parameter is mutually exclusive with the FIELD=fieldname parameter. The VALUE=value and GENERIC=value parameters cannot be used for a sequence by RID.

FIELD=fieldname

This parameter indicates that the sequence being defined is to be by the named key field.

KEY=fieldname

This option indicates that a keyed field histogram sequence is wanted. fieldname must be keyed, and not a sequence key. The resulting sequence is read using &NDBGET on the defined sequence. Rather than return the associated record (as gets on SEQ FIELD= does), the field value is retrieved, and the count of records having that value is also returned.

When retrieving, the FORMAT operand of GET is ignored. It must be specified to satisfy GET syntax, but (for example) FORMAT NO or FORMAT * 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.

FROM=value

This parameter indicates the (inclusive) starting value of either RID, or the named key field, for the sequence. The provided value must be valid for the field format, or a valid number from 1 to 1 billion for RID. The value is quoted, if required.

If this parameter is coded, the GENERIC and VALUE parameters cannot be specified.

Omission of this parameter, as well as the GENERIC and VALUE parameters (for FIELD=fieldname) implies the lowest possible value for the field format (or RID).

TO=value

This parameter indicates the (inclusive) ending value of either RID, or the named key field, for the sequence. The provided value must be valid for the field format, or a valid number from 1 to 1 billion for RID. The value is quoted, if required.

If this parameter is coded, the GENERIC and VALUE parameters cannot be specified.

Omission of this parameter, as well as the GENERIC and VALUE parameters (for FIELD=fieldname) implies the highest possible value for the field format (or RID).

VALUE=value

This parameter indicates the FROM and TO values are to be the same value, as specified. This is useful when reading by a non-unique key, top obtain all records with a given equal key value.

Note: This is not the same as GENERIC=value. If this parameter is coded, the GENERIC, FROM, and TO parameters cannot be coded.

This parameter is invalid for a sequence by RID.

GENERIC=value

This parameter indicates a sequence of all records with the nominated key field generically equal to the passed key value.

If this parameter is coded, the VALUE, FROM, and TO parameters cannot be coded.

This parameter is invalid for a sequence by RID, or for a sequence by a NUM or DATE format field.

KEEP= { NO | YES }

This parameter indicates whether the defined sequence is to be kept when an &NDBGET returns an end-of-file condition (&NDBRC=2).

KEEP=NO (the default) indicates the sequence is to be deleted at EOF.

KEEP=YES indicates the sequence is to be retained until explicitly deleted, or until an &NDBCLOSE for the database. The &NDBSEQ RESET statement is useful in this case.

DELETE

This parameter indicates an existing sequence, or all defined sequences for this database, is to be deleted.

SEQUENCE={ seqname | * }

The name of the sequence to delete, if present, or all sequences for this database, if '*' is coded.

RESET

This parameter indicates an existing sequence is to be reset, which removes any current end-of-file, and, optionally, positioned to a particular place within the sequence.

REPOS=value

This optional parameter allows a sequence to be positioned to a particular place. The next &NDBGET will retrieve the record with the key field (or RID) equal to, or nearest to (depending on the direction), the REPOS value. This value must be in a suitable format for the defined sequence field, or RID.

For sequences created with &NDBSCAN, this parameter is only valid if the scan had

SORT=fieldname specified. In this case, fieldname sets the format of the REPOS value.

RID=n

This optional parameter allows a sequence built by &NDBSCAN only to be repositioned to a specific RID, if it is in the result list. If not, response 1 will be returned.

RELPOS=n

This optional operand allows a sequence to be positioned to relative record n. n must be a number in the range from 1 to the number of records in the sequence.

Examples: &NDBSEQ

The following example defines a sequence that is used to read the entire database sequentially, in RID order.

&NDBSEQ MYNDB DEFINE SEQUENCE=S1 RID

The next example defines a sequence that is used to read all records on the database with field SURNAME equal to the value SMITH.

&NDBSEQ MYNDB DEFINE SEQ=S2 FIELD=SURNAME VALUE='SMITH'

The next example defines a sequence that is used to read all records on the database with field SURNAME generically equal to the value SMITH. This would include SMITHSON, SMITHE, and SMITH-WADDINGTON, for example.

&NDBSEQ MYNDB DEFINE SEQ=S3 FIELD=SURNAME GENERIC='SMITH'

The next example could be used to reset the sequence S2, defined previously, if part-way through reading all the SMITH records, to allow restarting from the beginning (or end, if &NDBGET DIR=BWD is used).

Note: If an end-of-file response had been returned, the reset would fail, as the sequence definition did not specify KEEP=YES, and thus would have been deleted.

&NDBSEQ MYNDB RESET SEQ=S2

Notes:

Errors encountered whilst processing the &NDBSEQ 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 successful define, delete, or reset will set &NDBRC to 0.

More information:

&NDBGET

&NDBSCAN