Positioning in a file using the SELECT command

Date:	Archived
Product/Release:	LANSA for the AS/400
Abstract:	Using the number of keys parameter of the SELECT command instead of *STARTKEY WHERE( ) and defining work fields to save the values of the key fields
Submitted By:	Danny Lenders

Many functions exist in which records are read from a file and displayed one page at a time in a sub-file (or browse list as it is called in LANSA). To select records for a particular number of main keys work fields are defined to save the value of each main key field and use the SELECT command with *STARTKEY WHERE( ), and the main key fields are equal to the saved values. The more key fields, the more work fields are defined. However, the same result can be achieved without having to define or even test the value of any work fields but by using the number of keys parameter of the SELECT command.

For example, a list of all the employees in a particular section of a particular department is to be displayed. The file used is PSLMST1 in the DEM partition, which has 3 key fields - DEPTMENT, SECTION and EMPNO.

Define two work fields in the data dictionary or in the function:

Define maximum number of keys that the file has

DEFINE FIELD(#NBRKEYS) TYPE(*DEC) LENGTH(3) DECIMALS(0)

The actual number of keys to be used when reading the file

DEFINE FIELD(#USEKEYS) TYPE(*DEC) LENGTH(3) DECIMALS(0)

Initialise the value to the number of keys in the file

CHANGE FIELD(#NBRKEYS) TO(3)
			BEGIN_LOOP

Now set the value of the number of keys to be used

For the very first read, EMPNO will not contain a value, so it should not be used

IF_NULL FIELD(#EMPNO)

			CHANGE FIELD(#USEKEYS) TO(�#NBRKEYS - 1')

			ELSE

Otherwise, use the field EMPNO to read the correct record

CHANGE FIELD(#USEKEYS) TO(#NBRKEYS)

			ENDIF

Clear the previously displayed sub-file

CLR_LIST NAMED(#BL_PSL)

Read enough records to fill one page

SELECT FIELDS(#BL_PSL) FROM_FILE(PSLMST1) WHERE(�#LISTENTRY < #LISTPAGE') WITH_KEY(#DEPTMENT #SECTION #EMPNO) NBR_KEYS(#USEKEYS) OPTIONS(*ENDWHERE)

Add details to sub-file

ADD_ENTRY TO_LIST(#BL_PSL)

Now set the number of keys so that EMPNO is no longer used

IF COND(�#NBRKEYS = #USEKEYS')

			CHANGE FIELD(#USEKEYS) TO(�#NBRKEYS - 1')

			ENDIF