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:
|
Company (#COMP) |
Division (#DIV) |
Department (#DEPT) |
Expenditure (#EXPEND) |
Revenue (#REVNU) |
|
01 |
1 |
ADM |
400 |
576 |
|
" |
" |
MKT |
678 |
56 |
|
" |
" |
SAL |
123 |
6784 |
|
|
|
|
|
|
|
" |
2 |
ADM |
46 |
52 |
|
" |
" |
SAL |
978 |
456 |
|
|
|
|
|
|
|
" |
3 |
ACC |
456 |
678 |
|
" |
" |
SAL |
123 |
679 |
|
|
|
|
|
|
|
02 |
1 |
ACC |
843 |
400 |
|
" |
" |
MKT |
23 |
0 |
|
" |
" |
SAL |
876 |
10 |
|
|
|
|
|
|
|
" |
2 |
ACC |
0 |
43 |
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)