SELECT

Extensions to the SELECT statement.

  Return to Contents Page   Return to Knowledge Base   Return to JAC Home Page

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