ISELECT

Generates a list of file items whose index keys meet specified conditions. The list is sorted in index key order.

Command Class

TCL-I command.

Syntax

ISELECT file-specifier WITH index-name {{operator} literalValue} {KEYS-ONLY} {VMC-SUPP} {(options}

ISELECT file-specifier WITH index-name [GT || GE] literalValue AND [LT || LE] literalValue {KEYS-ONLY} {VMC-SUPP} {(options}

Syntax Elements

file-specifier Specifies a file as defined in Conventions, except that file modifiers cannot be used because indexes are only definable on data sections.

index-name The name of an index associated with the file.

operator One of the following relational operators: EQ, LT, LE, GT or GE. If the operator and literal value are omitted, all items in the index are selected. If a literal value is specified, but no operator, the EQ operator is used.

literalValue A literal string enclosed in either single or double quotes. If the operator is EQ, a trailing ] character is interpreted as a wildcard (see Wildcard Searching).

KEYS-ONLY Specifies that the keys are returned instead of the item ids. Each item is multivalued, with the key followed by the total item count for that key. Overrides the effect of any options.

VMC-SUPP Specifies that the item-ids are returned, but not any value or subvalue offsets. Has the same effect as the I option.

Options

K Specifies that only the keys are returned, unless the I, V or S options are included (cf. the KEYS-ONLY keyword, which overrides the effect of the I, V and S options).

I The same as the VMC-SUPP keyword.

V Include only the value offset with each item-id. Do not include any subvalue offset. Overrides the I option.

S Include the value and sub-value offset with each item-id. Overrides the I and V options.

Description

The ISELECT command is similar to a normal English select command in that it creates a select list of item-ids which can be saved or passed to an English or TCL-II command or to a DataBasic program for further processing. The differences are that the ISELECT command uses a file index to perform this function and the list is sorted by index key.

ISELECT cannot work from an active select list. If a select list is active when ISELECT is invoked, the message Abandoning previous select list [ISELECT] is displayed and the list is discarded before creating the select list from the index.

On completion, ISELECT generates the following message:

itemCount item(s) selected from keyCount key(s).

where itemCount is the number of items meeting the selection criteria and keyCount is the number of unique keys.

The list is immediately active and can be used for further processing, or saved and retrieved for processing at a later time with the SAVE-LIST and GET-LIST commands.

The following example selects all entries from the index XNAME associated with the data file CUST.FILE.

ISELECT CUST.FILE WITH XNAME

Select-list Format

If ISELECT is used with no keywords or options, the format of the items in the list depends on the sort criteria used in the index definition:

If a single value sort connective was used only the item-ids are listed. No value or subvalue offsets are included.
If a value sort connective was used items in the list are multivalued, with the first value containing the item-id and the second the value offset (that is, number of the value used in the sort connective).
If a subvalue sort connective was used the items are similar, but the value offset is followed by a subvalue mark and the subvalue offset (that is, the number of the subvalue used in the sort connective).

Item-ids returned by an ISELECT command are sorted in the order of the index itself; that is, in ascending index key/item-id order.

This format can be altered with the KEYS-ONLY and VMC-SUPP keywords (see Syntax Elements), and the K, I, S and V options. Note that the KEYS-ONLY keyword overrides the effect of any options.

Using Selection Criteria

To select a subset of the indexed items, specify selection criteria; that is, an operator and a literal string value to match. Valid operators are EQ, LT, GT, GE, and LE.

The following example selects the portion of the CUST.FILE with key values in the index XZIP.NAME that are greater than 60419:

ISELECT CUST.FILE WITH XZIP.NAME GT "60419"

More complex matching can be performed by combining selection criteria with the AND connective. Under these circumstances, you must use GE or GT to specify the lower limit and LE or LT the upper limit of the required range of keys (see Syntax); the EQ operator is not valid. For example, to select the portion of CUST.FILE with key values in the index XZIP.NAME that are greater than 60419 and less than 70110, use the following command:

ISELECT CUST.FILE WITH XZIP.NAME GT "60419" AND LT "70110"

Note: Using the AND connective with ISELECT selects keys that fall between specified values; that is, keys that are greater than or greater than or equal to the lower key value and less than or less than or equal to the higher key value. The keys meeting this condition represent a contiguous group of keys within the index. Any other form for multiple selection criteria is invalid and produces an error message.

Wildcard Searching

When using the EQ operator, the literal value can end with the ] wildcard character. The following example selects key values that begin with 604.

ISELECT CUST.FILE WITH XZIP.NAME EQ "604]"