SELECTLEFT, SELECTRIGHT

The SELECTLEFT and SELECTRIGHT statements create a numbered select list from the entry in an alternate key index to the left or right of the last entry processed. The SELECTLEFTV and SELECTRIGHTV statements are similar but create a select list variable.

Format

SELECTLEFT index.name FROM file.var {SETTING key} {TO list.no}

SELECTRIGHT index.name FROM file.var {SETTING key} {TO list.no}

SELECTLEFTV index.name FROM file.var {SETTING key} TO list.var

SELECTRIGHTV index.name FROM file.var {SETTING key} TO list.var

where

index.name

is the name of the alternate key index to be processed.

file.var

is the file variable associated with an open file.

key	is the variable to be set to the key value associated with the returned list.

list.no

is the select list number of the list to be created. If omitted, select list zero is used.

list.var

is the select list variable to be created.

The SELECTLEFT and SELECTRIGHT statements construct a select list from the alternate key index entry to the left or right of the one most recently returned using SELECTINDEX, SELECTLEFT or SELECTRIGHT. The position of the scan can be set to the extreme left using SETLEFT or the extreme right using SETRIGHT.

These operations allow a program to find a specific value and then walk through successive values in the sorted data structure that makes up an alternate key index.

If SELECTINDEX is used to locate a value that does not exist in the index, SELECTLEFT will return a list of records for the value immediately before the non-existent one and SELECTRIGHT will return a list of records for the value immediately after the non-existent one.

The STATUS() function returns zero if the operation is successful, non-zero if it fails. Although other errors may occur, two useful status values are

3019	ER$AKNF	Specified index does not exist
3030	ER$EOF	No further items at end of index

The @SELECTED variable is set to the number of entries in the returned list, or zero if there are no further index entries to be returned.

Use of these statements inside a transaction will not reflect any uncommitted updates to the file.

See the SELECTINDEX statement for an example that describes how an index is sorted.

Examples

KEY = 'M'

SELECTINDEX 'POSTCODE', KEY FROM CLIENTS.FILE

LOOP

READNEXT CLIENT.NO ELSE EXIT

CRT CLIENT.NO

REPEAT

SELECTRIGHT 'POSTCODE' FROM CLIENTS.FILE SETTING POSTCODE

WHILE @SELECTED

WHILE POSTCODE[1,LEN(KEY)] = KEY

REPEAT

This program displays a list of all clients with postcodes beginning with M.

The SELECTINDEX looks for an index entry for a postcode of "M". This is unlikely to exist and hence the select list will probably be empty. If it did find any records, the inner loop would display these. Having processed this initial list, the SELECTRIGHT moves one step right (i.e. in ascending order) through the index tree and builds a list of these records. The POSTCODE variable is returned as the value of the indexed item located. Processing continues until the SELECTRIGHT finds an item that does not begin with the characters in KEY.

SELECTINDEX 'TIME', TIMESTAMP FROM LOG.F TO 1

IF @SELECTED = 0 THEN

SELECTLEFT 'TIME' FROM LOG.F TO 1

END IF

The above program fragment finds the record in the file open as LOG.F with the TIME field equal to TIMESTAMP. If there is no such record it finds the record with the nearest time before the requested timestamp. If multiple records have the same timestamp value, select list 1 will contain all of their ids. If TIME was the id of records in the log file, the select list could never contain multiple values.

See Alternate Key Indices for an example of how to perform partial key matching as a user enters characters of an item to be located via the index.