5.1 Using Working Lists for Summary Reports
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.