7 83 3 SELECT Examples

7.83.3 SELECT Examples

Example 1: Select and print fields #ORDLIN, #PRODUCT, #QUANTITY and #PRICE from all records in an order lines file which have an order number matching that specified in field #ODRNUM.

SELECT    FIELDS(#ORDLIN #PRODUCT #QUANTITY #PRICE) FROM_FILE(ORDLIN) WITH_KEY(#ORDNUM)
UPRINT    FIELDS(#ORDLIN #PRODUCT #QUANTITY #PRICE)
ENDSELECT
 

or identically:

GROUP_BY  NAME(#ORDERLINE) FIELDS(#ORDLIN #PRODUCT #QUANTITY #PRICE)
 
SELECT    FIELDS(#ORDERLINE) FROM_FILE(ORDLIN) WITH_KEY(#ORDNUM)
UPRINT    FIELDS(#ORDERLINE)
ENDSELECT
 

Example 2: Select and print fields #ORDLIN, #PRODUCT, #QUANTITY and #PRICE from all records in an order lines file which have a #QUANTITY value greater than 10 or a #PRICE value less than 49.99

SELECT    FIELDS(#ORDLIN #PRODUCT #QUANTITY #PRICE) FROM_FILE(ORDLIN) WHERE('(#QUANTITY *GT 10) *OR (#PRICE *LT 49.99)')
UPRINT    FIELDS(#ORDLIN #PRODUCT #QUANTITY #PRICE)
ENDSELECT
 

or identically:

GROUP_BY  NAME(#ORDERLINE) FIELDS(#ORDLIN #PRODUCT #QUANTITY #PRICE)
 
SELECT    FIELDS(#ORDERLINE) FROM_FILE(ORDLIN) WHERE('(#QUANTITY *GT 10) *OR (#PRICE *LT 49.99)')
UPRINT    FIELDS(#ORDERLINE)
ENDSELECT
 

Example 3: If a file called ACCOUNT contains the following fields and data:

and the file is keyed by #COMP, #DIV and #DEPT then use the NBR_KEYS parameter of the SELECT command to create a very flexible browse function:

DEF_LIST   NAME(#ACCOUNTS)  FIELDS(#COMP #DIV #DEPT #EXPEND #REVNU)
DEFINE     FIELD(#NBRKEYS) TYPE(*DEC) LENGTH(1) DECIMALS(0)
 
BEGIN_LOOP
 
CHANGE     (#COMP #DIV #DEPT) *NULL
REQUEST    FIELDS(#COMP #DIV #DEPT)
 
IF_NULL    FIELD(#COMP #DIV #DEPT)
CHANGE     #NBRKEYS 0
ELSE
IF_NULL    FIELD(#DIV #DEPT)
CHANGE     #NBRKEYS 1
ELSE
IF_NULL    FIELD(#DEPT)
CHANGE     #NBRKEYS 2
ELSE
CHANGE     #NBRKEYS 3
ENDIF
ENDIF
ENDIF
CLR_LIST   NAMED(#ACCOUNTS)
SELECT     FIELDS(#ACCOUNTS) FROM_FILE(ACCOUNT) WITH_KEY(#COMP #DIV #DEPT) NBR_KEYS(#NBRKEYS)
ADD_ENTRY  TO_LIST(#ACCOUNTS)
ENDSELECT
 
DISPLAY    BROWSELIST(#ACCOUNTS)
 
END_LOOP
 

If the user does not input any values at the REQUEST command,  then #NBRKEYS will contain 0 when the SELECT command is executed, so in effect the SELECT command that is being executed is:

SELECT     FIELDS(#ACCOUNTS) FROM_FILE(ACCOUNT)
 

which causes all the records in the file to be displayed.

If the user inputs a value for #COMP at the REQUEST command,  then #NBRKEYS will contain 1 when the SELECT command is executed, so in effect the SELECT command that is being executed is:

SELECT  FIELDS(#ACCOUNTS) FROM_FILE(ACCOUNT) WITH_KEY(#COMP)
 

which causes all the records in the file that have the requested company number to be displayed.

If the user inputs a value for #COMP and a value for #DIV at the REQUEST command, then #NBRKEYS will contain 2 when the SELECT command is executed, so in effect the SELECT command that is being executed is:

SELECT  FIELDS(#ACCOUNTS) FROM_FILE(ACCOUNT) WITH_KEY(#COMP #DIV)
 

which causes all the records in the file that have the requested company number and division number to be displayed.

If the user inputs a value for #COMP, a value for #DIV and a value for #DEPT at the REQUEST command, then #NBRKEYS will contain 3 when the SELECT command is executed, so in effect the SELECT command that is being executed is:

SELECT   FIELDS(#ACCOUNTS) FROM_FILE(ACCOUNT) WITH_KEY(#COMP #DIV #DEPT)
 

which causes all the records in the file that have the requested company number, division number and department number to be displayed. For the data specified, only one record would ever be displayed in this case.

Example 4: Produce a functionally identical solution to example 3 by using the NBR_KEYS(*COMPUTE) parameter:

DEF_LIST   NAME(#ACCOUNTS)  FIELDS(#COMP #DIV #DEPT #EXPEND #REVNU)
 
BEGIN_LOOP
CHANGE     (#COMP #DIV #DEPT) *NULL
REQUEST    FIELDS(#COMP #DIV #DEPT)
CLR_LIST   NAMED(#ACCOUNTS)
SELECT     FIELDS(#ACCOUNTS) FROM_FILE(ACCOUNT) WITH_KEY(#COMP #DIV #DEPT) NBR_KEYS(*COMPUTE)
ADD_ENTRY  TO_LIST(#ACCOUNTS)
ENDSELECT
DISPLAY    BROWSELIST(#ACCOUNTS)
END_LOOP
 

Example 5: Select and print fields #ORDLIN, #PRODUCT, #QUANTITY and #PRICE from all records in an order lines file which have an order number matching that specified in field #ODRNUM. Print the information in reverse order (ie: highest line number first).

GROUP_BY  NAME(#ORDERLINE) FIELDS(#ORDLIN #PRODUCT #QUANTITY #PRICE)
 
SELECT    FIELDS(#ORDERLINE) FROM_FILE(ORDLIN)  WITH_KEY(#ORDNUM) OPTIONS(*BACKWARDS)
UPRINT    FIELDS(#ORDERLINE)
ENDSELECT
 

Example 6: Use exactly the same logic as example 5, but ensure that no more than 3 lines are ever printed.

GROUP_BY  NAME(#ORDERLINE) FIELDS(#ORDLIN #PRODUCT #QUANTITY #PRICE)
 
CHANGE    FIELD(#COUNTER) TO(0)
SELECT    FIELDS(#ORDERLINE) FROM_FILE(ORDLIN) WITH_KEY(#ORDNUM) WHERE('#COUNTER *LT 3') OPTIONS(*BACKWARDS *ENDWHERE)
UPRINT    FIELDS(#ORDERLINE)
CHANGE    FIELD(#COUNTER) TO('#COUNTER + 1')
ENDSELECT
 

Example 7: Ask the user to input a customer name. Then select and display details of the first 10 names from a name and address file that are "closest" to the nominated name.

DEF_LIST  NAME(#CUSTOMER) FIELDS(#NAME #CUSTNO #ADDR1 #POSTCODE) COUNTER(#NUMCUSTS)
 
REQUEST   FIELDS(#NAME)
CLR_LIST  NAMED(#CUSTOMER)
SELECT    FIELDS(#CUSTOMER) FROM_FILE(NAMES) WITH_KEY(#NAME) WHERE('#NUMCUSTS *LT 10') OPTIONS(*STARTKEY *ENDWHERE)
ADD_ENTRY TO_LIST(#CUSTOMER)
ENDSELECT
DISPLAY   BROWSELIST(#CUSTOMER)
 

Example 8: Select all fields from the currently active version of file ORDLIN, perform diverse calculations involving all fields from the file and print the results for each selected record.

SELECT    FIELDS(*ALL) FROM_FILE(ORDLIN) WITH_KEY(#ORDNUM)    
.......
.......
.......
 
UPRINT    FIELDS(#RESULT1 #RESULT2 #RESULT3)