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{ AND operator literalValue}} {KEYS-ONLY}
{
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, =is equal to
GT, >is greater than
GE, >=is greater then of equal to
LT, <is less than
LE, <=is less than or equal to
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 One or more literal strings enclosed in either single or double quotes. Include one string for each BY clause in the index definition.
If the operator is EQ or =, a trailing ] character is interpreted as a wildcard (see Wildcard Searching).
AND Specifies that ISELECT should count 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.
Note that you cannot use the EQ or = operator with AND.
KEYS-ONLY Specifies that the keys are returned instead of the item-ids. Each item is multivalued, with the keys followed by the total item count for that key. Overrides the effect of any options.
Options
K Specifies that only the keys are
returned, unless the
I,
V or
S options are included (cf. the
I The same as the VMC-SUPP keyword.
V Include only the value
offset with each
S Include the value and
sub-value offset with each
Operation
The ISELECT command is similar to a normal English select command in that it
creates a select list of
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/
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, and their symbol equivalents (see Syntax).
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 or = 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]"
Output Conversions
For indexes that were created from dictionary definitions that include output conversions, ISELECT applies those output conversions as input conversions to the literal data specified as appropriate. For backwards compatibility, this behaviour can be inhibited with the EXTERNAL.KEY environment option.
Case Sensitivity
If keyword case-insensitivity is selected, the WITH, EQ, GT, GE, LT, LE and AND keywords can be in upper or lower case. Otherwise, they must be in upper case.
The case of the literalValue parameter is subject to the case-sensitivity settings that were current when the index was created (see CREATE-INDEX, Item Lists and Selection Criteria). If in doubt, ensure that the case of this parameter matches that of the index key values.