Page History

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     
 

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.