7 83 SELECT

LANSA Technical

7.83 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.

Portability Considerations

Refer to parameters FROM_FILE , GENERIC , LOCK and OPTIONS .

 

Also See

7.83.1 SELECT Parameters

7.83.2 SELECT Comments / Warnings

7.83.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