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:
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:
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.
|
Portability Considerations |
Refer to parameters FROM_FILE , GENERIC , LOCK and OPTIONS . |
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
