Progress
Programming
Handbook


Temporary Table Example

The following procedure creates a temporary table (temp–item) that stores the total inventory value (the sum of item.price * item.on–hand) for each item’s catalog page (item.cat–page). It builds a temp–item with two indexes: one that sorts the table in ascending order by catalog page and a second that sorts the table in descending order by inventory value.

This shows how you can use a temporary table to store a calculated result from the database, and efficiently report the same result according to different sorting and selection criteria:

p-tmptbl.p
DEFINE TEMP-TABLE temp-item
   FIELD cat-page LIKE item.cat-page
   FIELD inventory LIKE item.price LABEL "Inventory Value"
   INDEX cat-page IS PRIMARY cat-page ASCENDING
   INDEX inventory-value inventory DESCENDING.

DEFINE VARIABLE cutoff LIKE item.price.
DEFINE VARIABLE inv-price LIKE item.price.
DEFINE VARIABLE report-type AS INTEGER INITIAL 1.
      
FORM
   cutoff LABEL "Inventory Lower Cutoff for each Catalog Page"
      AT ROW 1.25 COLUMN 2
   report-type LABEL "Report Sorted ..."  AT ROW 2.25 COLUMN 2 
      VIEW-AS RADIO-SET RADIO-BUTTONS
        "By Catalog Page", 1, "By Inventory Value", 2
   WITH FRAME select-frame SIDE-LABELS WIDTH 70 TITLE "Specify Report ..."
      VIEW-AS DIALOG-BOX.

FOR EACH item BREAK BY item.cat-page:
   ACCUMULATE price * on-hand (SUB-TOTAL BY item.cat-page).
   IF LAST-OF(item.cat-page) THEN DO:
      inv-price = ACCUM SUB-TOTAL BY item.cat-page (price * on-hand).
      CREATE temp-item.
      temp-item.cat-page = item.cat-page.
      inventory     = inv-price.
   END.
END. /* FOR EACH item */

REPEAT:

   UPDATE cutoff report-type WITH FRAME select-frame.

   IF report-type = 1 THEN
      FOR EACH temp-item USE-INDEX cat-page WITH FRAME rpt1-frame:
         IF inventory >= cutoff THEN
            DISPLAY temp-item.cat-page inventory.
      END.
   ELSE
      FOR EACH temp-item USE-INDEX inventory-value WITH FRAME rpt2-frame:
         IF inventory >= cutoff THEN
            DISPLAY temp-item.cat-page inventory.
      END.
END. /* REPEAT */ 


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