Where a "summary" style report is to be produced, and a relatively small number of summary records are to be printed (ie: less than 1000), then the LANSA object called a "working" list is a viable option.
In many situations, working lists save having to create additional access paths over database files (either directly via a logical file or by using the OPNQRYF option).
For example, consider the following instance where a "departmental expenditure" file called EXPEND is read:
FILE: "EXPEND" Company Department Amount Number Number Spent #COMPNO #DEPNO #AMOUNT 02 11 276.35 01 14 100.12 01 17 764.37 02 12 1945.24 01 19 89.12 02 14 568.23 01 15 375.89 etc, etc .......
To produce a summary of expenditure by company, we could first define a "working list" called #SUMMARY as follows:
DEFINE FIELD(#SUMCOMPNO) REFFLD(#COMPNO)
DEFINE FIELD(#SUMAMOUNT) REFFLD(#AMOUNT)
DEF_LIST NAME(#SUMMARY) FIELDS(#SUMCOMPNO #SUMAMOUNT)
TYPE(*WORKING) NBR_ENTRYS(50)
This working list can be "visualized" as a multiple occurrence structure (or array):
--------------------------------- | Entry | Company | Amount | | Number | Number | Spent | |(implicit) |#SUMCOMPNO|#SUMAMOUNT| |---------- | -------- | ---------| | 001 | | | | 002 | | | | 003 | | | | " | | | | 050 | | | ---------------------------------
Note that this allows for 50 different companies to be processed. If we attempted to process 51, the program would fail with a very specific error message indicating that the working list is full.
Now we can read all the "departmental expenditure" records and summarize them into the list called #SUMMARY:
SELECT FIELD(#COMPNO #AMOUNT) FROM_FILE(EXPEND)
LOC_ENTRY IN_LIST(#SUMMARY) WHERE('#SUMCOMPNO = #COMPNO')
IF_STATUS IS(*OKAY)
CHANGE FIELD(#SUMAMOUNT) TO('#SUMAMOUNT + #AMOUNT')
UPD_ENTRY IN_LIST(#SUMMARY)
ELSE
CHANGE FIELD(#SUMCOMPNO) TO(#COMPNO)
CHANGE FIELD(#SUMAMOUNT) TO(#AMOUNT)
ADD_ENTRY TO_LIST(#SUMMARY)
ENDIF
ENDSELECT
Note: The data can be read from file EXPEND in any order.
Next, we could sort the list to produce the summary report in company number order:
DEF_LINE NAME(#LINE01) FIELDS(#SUMCOMPNO #SUMAMOUNT)
SORT_LIST NAMED(#SUMMARY) BY_FIELDS(#SUMCOMPNO)
SELECTLIST NAMED(#SUMMARY)
PRINT LINE(#LINE01)
ENDSELECT
Finally, we could re-sort the list in descending order of amount spent, and produce another summary report (ie: companies ranked from highest spender to lowest spender):
DEF_LINE NAME(#LINE02) FIELDS(#SUMAMOUNT #SUMCOMPNO)
SORT_LIST NAMED#SUMMARY) BY_FIELDS((#SUMAMOUNT *DESCEND))
SELECTLIST NAMED(#SUMMARY)
PRINT LINE(#LINE02)
ENDSELECT
Some general notes about using working lists:
- Working lists are treated like database files in many ways, you can add data to them, update data already in them or clear them.
- The WHERE parameter can contain complex expressions. For instance the previous example could have used a parameter
WHERE('(#SUMCOMPNO = #COMPNO) OR ((#SUMAMOUNT 1.34) <
#AMOUNT) *OR (#SUMCOMPNO = 99)').
This is a rather nonsensical expression, but it demonstrates the power of the LOC_ENTRY command.
- The SORT_LIST command can sort the list over more than one field, and can also support ascending or descending sorting.