7.107 SELECT

Note: Usage options

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.

Portability Considerations

Refer to parameters FROM_FILE, GENERIC, LOCK, and OPTIONS.

Also See

7.107.1 SELECT Parameters

7.107.2 SELECT Comments / Warnings

7.107.3 SELECT Examples

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