Using a temp-table to summarize data

The beginning of this chapter notes two basic purposes for temp-tables: first, to define a table unlike any single database table for data summary or other uses; and second, to pass a set of data as a parameter between procedures. In this section, you will work through an example of the first kind. You will write a procedure that defines a temp-table and uses it to total invoice amounts for each Customer, and at the same time to count the number of Invoices for each Customer and identify which one has the highest amount. The finished procedure is saved as h-InvSummary.p.

In addition to the Customer table you are familiar with, the example uses the Invoice table in the Sports2000 database. The Invoice table holds information for each Invoice a Customer has been sent for each Order. It has a join to the Customer table and to the Order table, along with the date and amount of the Invoice and other information.

First is the statement to define the temp-table itself:

/* h-InvSummary.p -- Build a summary report of customer invoices. */
DEFINE TEMP-TABLE ttInvoice
  FIELD iCustNum    LIKE Invoice.CustNum LABEL "Cust#" FORMAT "ZZ9"
  FIELD cCustName   LIKE Customer.NAME FORMAT "X(20)"
  FIELD iNumInvs    AS INTEGER LABEL "# Inv's" FORMAT "Z9"
  FIELD dInvTotal   AS DECIMAL LABEL "Inv Total  " FORMAT ">>,>>9.99"
  FIELD dMaxAmount  AS DECIMAL LABEL "Max Amount   " FORMAT ">>,>>9.99"
  FIELD iInvNum     LIKE Invoice.InvoiceNum LABEL "Inv#" FORMAT "ZZ9"
  INDEX idxCustNum IS PRIMARY iCustNum
  INDEX idxInvTotal dInvTotal.

The procedure creates one record in the temp-table for each Customer, summarizing its Invoices. As you can see, the temp-table has these fields:

A Customer Name field derived from that field in the Customer table. Later you'll use the Customer Number to retrieve the Customer record so that you can add the name to the invoice information.

The field definitions define or override the field label and default format in some cases, using phrases attached to the FIELD definition. By default, the right-justified label for a numeric field extends somewhat to the right of the data, which in the case of the InvTotal and MaxAmount fields doesn't look quite right, so the extra spaces in their labels correct that.

The temp-table also has two indexes. The first orders the records by Customer Number. This index is useful because the code finds records based on that value to accumulate the Invoice total and other values. This is the primary index for the temp-table, so if you display or otherwise iterate through the temp-table records without any other specific sort, they appear in Customer Number order.

The second index is by the Invoice Total. This index is useful because the procedure uses it as the sort order for the final display of all the records.

The first executable code begins a FOR EACH block that joins each Invoice record to its Customer record. The OF phrase uses the CustNum field that the two tables have in common to join them. The FIND statement checks to see whether there is already a temp-table record for the Customer. If there is no FIND statement, it uses the CREATE statement to create one. This statement creates a new record either in a database table or, as you see here, in a temp-table. That new record holds the initial values of the fields in the table until you set them to other values.

After the new record is created, the code sets the key value (the iCustNum field) and saves off the Customer Name from that table. The ASSIGN statement lets you make multiple field assignments at once and is more efficient than a series of statements that do one field assignment each:

/* Retrieve each invoice along with its Customer record, to get the Name. */
FOR EACH Invoice, Customer OF Invoice:
  FIND FIRST ttInvoice WHERE ttInvoice.iCustNum = Invoice.CustNum NO-ERROR.
  /* If there isn't already a temp-table record for the Customer, create it
     and save the Customer # and Name. */
  IF NOT AVAILABLE ttInvoice THEN DO:
    CREATE ttInvoice.
    ASSIGN
      ttInvoice.iCustNum = Invoice.CustNum
      ttInvoice.cCustName = Customer.Name.
  END.

Next, the code compares the Amount of the current Invoice with the dMaxAmount field in the temp-table record, which is initially 0 for each newly created record). If the current Amount is larger than that value, it's replaced with the new Amount and the Invoice number is saved off in the iInvNum field. In this way, the temp-table records wind up holding the highest InvoiceAmount for the Customer after it has cycled through all the Invoices:

/* Save off the Invoice amount if it's a new high for this Customer. */
  IF Invoice.Amount > dMaxAmount THEN
    ASSIGN dMaxAmount = Invoice.Amount
           iInvNum    = Invoice.InvoiceNum.

Still in the FOR EACH loop, the code next increments the Invoice total for the Customer and the count of the number of Invoices for the Customer:

/* Increment the Invoice total and Invoice count for the Customer. */
ASSIGN ttInvoice.dInvTotal = ttInvoice.dInvTotal + Invoice.Amount
ttInvoice.iNumInvs = ttInvoice.iNumInvs + 1.
END. /* END FOR EACH Invoice & Customer */

Now the procedure has finished cycling through all of the Invoices, and it can take the summary data in the temp-table and display it, in this case with the Customer with the highest Invoice Total first:

/* Now display the results in descending order by invoice total. */
FOR EACH ttInvoice BY dInvTotal DESCENDING:
DISPLAY iCustNum cCustName iNumInvs dInvTotal iInvNum dMaxAmount.
END.

The following figure shows the first page of the output report you should see when you run the procedure.

Using the temp-table made it easy to accumulate different kinds of summary data and to combine information from different database tables together in a single report. You could easily display this data in different ways, for example sorted by different fields, without having to again retrieve it from the database.