SELECT

Extensions to the SELECT statement.

Notes:

When the SELECT statement is used with an index variable (created with the OPENINDEX statement) then it can be extended to the following syntax.

Syntax:

SELECT indexvariable {TO select-def} {ATKEY index-key{,record-key{,vmcount}}}

Description:

The extensions allow you to use the index data when used with an index definition variable created with an OPENINDEX statement. In its simplest form the following example shows how you can display all the record keys within a file that have a secondary index called "NAME" and thus the record keys will have been sorted according to the index definition for "NAME".

Examples:

OPENINDEX "CUSTOMERS" , "NAME" TO index.var ELSE

    STOP 201,"NAME in CUSTOMERS"

END

SELECT index.var

LOOP WHILE READNEXT record.key DO

    CRT record.key

REPEAT

You can use the normal "TO select-def" to perform the selection to a numbered list or a variable instead of to the default select list.

The use of the "ATKEY index-key{,record-key{,vmcount}} is optional and allows you to position yourselves at a point in the index other than at the start. In its minimal form you specify the index key at which to start your selection. As each index key may contain many record keys you can further sub-specify at which record key to within that index to position yourselves. Finally each record may be multi-valued with the same index data, you can finally sub-position yourselves at the value mark within the record key. This value should be the raw value without any use of lookup code. For example if the index was a date field, you would enter in "index-key" the numeric date such as "10223" instead of the external representation such as "22-AUG-1987".

For example, consider the following records written to a file with a simple left justified index created on a multi-valued attribute 1.

Record Key	Data in attribute 1
A	COOPER]SMITH]JONES]COOPER]COOPER
B	COOPER]CLARK
C	JONES

The following index data will now have been created for the above data

Index key	Index data
CLARK	Key "B" at multi-value 2
COOPER	Key "A" at multi-value 1 Key "A" at multi-value 4 Key "A" at multi-value 5 Key "B" at multi-value 1
JONES	Key "A" at multi-value 3 Key "C" at multi-value 1
SMITH	Key "A" at multi-value 2

The following are examples of jBC code on the above data and the resultant screen output along with comments on the action.

Example 1:

SELECT the entire index.

OPENINDEX filename,"INDEX1" TO index.var ELSE

    STOP 201,"INDEX1 in ":filename

END

SELECT index.var

LOOP WHILE READNEXT key DO

    CRT key

REPEAT

jsh-> test1
B
A
A
A
B
A
C
A

Note that the order of the record keys (B,A,A,A,B,A,C,A) is identical to the table of index information show.

Example 2:

SELECT the index but only include names "JONES"

OPENINDEX filename,"INDEX1" TO index.var ELSE

    STOP 201,"INDEX1 in ":filename

END

SELECT index.var ATKEY "JONES"

LOOP

    READNEXT KEY index.key,record.key ELSE index.key = ""

WHILE index.key EQ "JONES" DO

    CRT index.key,record.key

REPEAT

jsh-> test2
JONES A
JONES C

In this example we exit the loop once we run out of names of "JONES". Only two of these are present, the first in record key "A" and the second in record key "C".

Example 3:

SELECT the index starting at "JONES" but use READPREV to find all the names that precede JONES (i.e. do NOT include JONES)

OPENINDEX filename,"INDEX1" TO index.var ELSE

    STOP 201,"INDEX1 in ":filename

END

SELECT index.var ATKEY "JONES"

LOOP WHILE READPREV KEY index.key,record.key,mvno DO

    CRT index.key,record.key,mvno

REPEAT

jsh à test3
COOPER B 1
COOPER A 5
COOPER A 4
COOPER A 1
CLARK B 2

In this example the index keys are read in reverse order. As a SELECT is assumed to be in forward direction, the first READPREV constitutes a change of direction subject to the rules described in the READPREV description. Hence the first index key is the one that precedes the first JONES entry.

http://807199.827977/r5/knowledgebase/manuals/3.0/SecondaryIndexes/html/select.htm last modified on 06/19/09 05:29 AM