Progress
Programming
Handbook


Using Work Tables for Complex Sorting

Progress uses indexes to order database records. Although work tables have no indexes, you can position work table records where you want because whenever you create a work table record, Progress places it after the record that it found last in that work table. This flexibility lets you keep the work table records in a particular order without having to sort later in the procedure. For example, p-wrk4.p is a modified version of the p-wrk3.p procedure:

p-wrk4.p
DEFINE WORK-TABLE cpage
   FIELD w-cat-page LIKE item.cat-page
   FIELD w-inv-value AS DECIMAL FORMAT "->>>,>>>,>>9.99"
       LABEL "Inventory Value"
   FIELD w-item-value AS DECIMAL FORMAT ">>>,>>9.99"
       LABEL "Item Inv. Value"
   FIELD w-item-num LIKE item.item-num.
       
FOR EACH item:
   FIND FIRST cpage WHERE cpage.w-cat-page >= item.cat-page NO-ERROR.
   
   IF NOT AVAILABLE cpage OR cpage.w-cat-page > item.cat-page
   THEN DO:
      FIND PREV cpage NO-ERROR.
      CREATE cpage.
      cpage.w-cat-page = item.cat-page.
   END.
   
   IF price * on-hand > w-item-value
   THEN DO:
      ASSIGN w-item-value = price * on-hand
             w-item-num = item.item-num.
   END.
   
   cpage.w-inv-value = cpage.w-inv-value + (item.price * item.on-hand).
END.

FOR EACH cpage:
   DISPLAY w-cat-page w-inv-value w-item-num w-item-value.
END. 

This procedure uses the following logic to maintain the cpage work table in catalog page order:

  1. The FIND FIRST statement tries to find a cpage record whose catalog page is the same or greater than that of the current item.
  2. If the catalog page of the cpage record is larger than that of the current item, or if the cpage record does not exist, Progress finds the previous cpage record (if any) and creates the new cpage record after that record in the work table. If it finds a cpage record with the same product line as the item, it does not create a new cpage record, but uses the existing one.
  3. The procedure computes the necessary field values for the current cpage record.

Figure 15–3 shows how this procedure keeps the cpage work table in order by product line.

Figure 15–3: Maintaining Work Tables in Sorted Order

By maintaining the cpage table in catalog page order as the procedure creates new cpage records, there is no need to sort the table in the final FOR EACH block. When that block displays the cpage records, they are already sorted by catalog page.

There is another advantage to using this method of sorting as opposed to the method used in p-wrk3.p. Figure 15–4 illustrates this advantage.

p-wrk3.p
p-wrk4.p

Figure 15–4: Comparing Two Sorting Methods

In Method 2, because the records in the cpage table are already in sorted order, the find is much more efficient than in Method 1. For an item with a catalog page that already exists in the cpage table, both methods are equally efficient, on average.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095