7.107 SELECT
The SELECT command is used in conjunction with the ENDSELECT command to form a "loop" to process one or more records from a file that match certain criteria.
For example, the SELECT / ENDSELECT loop:
--->SELECT FIELDS(#ORDLIN #PRODUCT #QUANTITY)
| FROM_FILE(ORDLIN) WITH_KEY(#ORDER)
|
| DISPLAY FIELDS(#ORDER #ORDLIN #PRODUCT #QUANTITY)
|
----ENDSELECT
Forms a loop to read all records from file ORDLIN that have an order number matching the value in field #ORDER.
Each time a record is read the DISPLAY command, which is within the SELECT / ENDSELECT loop will display details of the record just read.
The SELECT command is probably the most flexible command in the LANSA RDML and some experience with it is required before the full power can be utilized. Some of the types of database processing supported by it include:
Entry sequence processing
Full key processing
Partial key processing
Generic key processing
Execution time modification of the number of keys to be used
Conditional selection of records
Forwards or backwards processing of selected records
Start at (or near) a key then process backwards or forwards
In addition, the SELECT command can be used in conjunction with the IBM i operating system command OPNQRYF (Open Query File). This extends the power of the SELECT command to include:
Execution time modification of record selection criteria
Execution time modification of the order records are processed
Field content searching
Field substringing during selection comparisons
Searching without regard to the case (upper or lower) of fields
For more details of how to use the IBM i operating system command OPNQRYF refer to the OPEN command in this guide first.
SELECT loop logic that should be avoided.
When fields A, B and C are selected in a SELECT loop like this:
SELECT FIELDS(#A #B #C)
FROM_FILE(...)
WHERE(...............)
.......
.......
ENDSELECT
they have a predictable and consistent value within the loop across all platforms.
These fields do not have a predictable and consistent value outside the loop. So this:
SELECT FIELDS(#A #B #C) FROM_FILE(...)
.......
IF COND(#A < 35.5)
.......
ENDIF
.......
ENDSELECT
is a predictable piece of logic, while:
SELECT FIELDS(#A #B #C)
FROM_FILE(...)
WHERE(...............)
.......
.......
ENDSELECT
IF COND(#A < 35.5)
.......
ENDIF
in any form or variation, is an unpredictable piece of logic.
The value of A (B and C), in terms of data read from the selection table, after exit from the SELECT loop, are actually defined as "not defined". This means that their values at the termination of a SELECT / ENDSELECT loop are not predictable or consistent across platforms.
Also See
7.107.2 SELECT Comments / Warnings
Required
SELECT ---------- FIELDS ---------- field name field attributes ----->
| | | |
| --- 7 max ---- |
|*ALL |
|*ALL_REAL |
|*ALL_VIRT |
|*INCLUDING |
|*EXCLUDING |
|expandable group |
| |
|------- 1000 max for RDMLX---|
------- 100 max for RDML ----
>-- FROM_FILE -------- file name . *FIRST ---------------->
library name
--------------------------------------------------------------------------
Optional
>-- WHERE ----------- 'condition' ------------------------>
>-- WITH_KEY --------- key field values ------------------>
expandable group expression
>-- NBR_KEYS -------- *WITHKEY ------------------------>
*COMPUTE
numeric field name
>-- GENERIC ---------- *NO ------------------------------>
*YES
>-- IO_STATUS -------- *STATUS -------------------------->
field name
>-- IO_ERROR --------- *ABORT -------------------------->
*NEXT
*RETURN
label
>-- VAL_ERROR ------- *LASTDIS --------------------------->
*NEXT
*RETURN
label
>-- END_FILE --------- *NEXT ------------------------------>
*RETURN
label
>-- ISSUE_MSG ------ *NO --------------------------------->
*YES
>-- LOCK ------------ *NO --------------------------------->
*YES
>-- RETURN_RRN --- *NONE ------------------------------->
field name
>-- OPTIONS ------- up to 5 options allowed --------------|
*BACKWARDS
*STARTKEY
*ENDWHERE
*ENDWHERESQL
*BLOCKnnn