You can now put these statements together to create a procedure that defines, fills, and displays data from the ProDataSet dsOrder. Procedure fillDSOrder.p follows this process:

  1. Includes the temp-table definitions and the ProDataSet definition you have already seen
  2. Defines the queries for the ttOrder table and the ttItem table (the second one is used later)
  3. Defines Data-Sources for all three temp-tables
  4. Prepares the top-level query for the ttOrder table to bring in Order number 1
  5. Attaches all three Data-Sources to the ProDataSet buffers
  6. Executes the FILL method on the ProDataSet handle
  7. Detaches the Data-Sources from the ProDataSet buffers, as shown in the following example:
    /* fillDSOrder.p -- Test procedure for an Order Dataset */
{dsOrderTT.i}
{dsOrder.i}

DEFINE QUERY qOrder FOR Order, Customer, SalesRep.
DEFINE QUERY qItem  FOR Item.

DEFINE DATA-SOURCE srcOrder FOR QUERY qOrder Order KEYS (OrderNum),
  Customer KEYS (CustNum), SalesRep KEYS (SalesRep). 
DEFINE DATA-SOURCE srcOline FOR OrderLine KEYS (OrderNum).
DEFINE DATA-SOURCE srcItem FOR Item KEYS (ItemNum).

QUERY qOrder:QUERY-PREPARE("FOR EACH Order WHERE Order.OrderNum = 1, " +
  "FIRST Customer OF Order, FIRST SalesRep OF Order").

BUFFER ttOrder:ATTACH-DATA-SOURCE(DATA-SOURCE srcOrder:HANDLE,
                                  "Customer.Name,CustName").
BUFFER ttOline:ATTACH-DATA-SOURCE(DATA-SOURCE srcOline:HANDLE).
BUFFER ttItem:ATTACH-DATA-SOURCE(DATA-SOURCE srcItem:HANDLE).

DATASET dsOrder:FILL().

BUFFER ttOrder:DETACH-DATA-SOURCE().
BUFFER ttOline:DETACH-DATA-SOURCE().
BUFFER ttItem:DETACH-DATA-SOURCE().

At this point Order number 1, its OrderLines, and their Items, are all in the ProDataSet temp-tables. You can verify this simply by displaying some of the fields in the temp-tables. For example:

FOR EACH ttOrder:
  DISPLAY 
    ttOrder.OrderNum 
    ttOrder.OrderDate 
    ttOrder.CustName FORMAT "X(15)"
    ttOrder.RepName FORMAT "X(15)".
END.
FOR EACH ttOline:
  DISPLAY 
    ttOline.OrderNum
    ttOline.LineNum.
END.
FOR EACH ttItem:
  DISPLAY ttItem.ItemNum ttItem.ItemName.
END.

When you run the previous procedure, you will see the results of the DISPLAY statements—the Order, as shown in the following figure.

Figure 1. Customer orders


The ttOline temp-table contains all of the OrderLines for Order 1, as shown in the following figure.

Figure 2. Customer order lines


The ttItem temp-table contains all of the Items used on any of those OrderLines, as shown in the following figure.

Figure 3. Customer order line items


This looks fine, but in fact, you were fortunate you did not get an error when you ran the procedure. Try changing the selection for the Order query to fill all Orders less than 10, as shown in this code snippet:

QUERY qOrder:QUERY-PREPARE("FOR EACH Order WHERE Order.OrderNum < 10, " +
  "FIRST Customer OF Order, FIRST SalesRep OF Order").

This procedure change will also fill all their OrderLines and all the Items used on any of those OrderLines. Run the procedure and display the Orders, OrderLines, and finally the Items, as shown in the following figure.

Figure 4. Duplicate customer order line items


Some of the Item records, such as Item 54, are represented more than once. This is because they are used in more than one OrderLine. You probably do not want them in your ttItem table more than once. Since MERGE is the default FILL-MODE, why did the AVM not eliminate the duplicates?

The answer is that you did not have an index on the ttItem table. The AVM eliminates duplicates in a ProDataSet temp-table by relying on the internal indexing mechanism. Because this temp-table is not defined to be LIKE a database table, it does not inherit the Item table's indexes automatically. For the AVM to eliminate duplicate Items from this temp-table should they occur, you need a unique index on the ItemNum field. Add an index to the temp-table definition in the include file dsOrderTT.i, as shown:

DEFINE TEMP-TABLE ttItem NO-UNDO
  FIELD ItemNum  LIKE ITEM.ItemNum
  FIELD ItemName LIKE ITEM.ItemName
  FIELD Price    LIKE ITEM.Price
  FIELD Weight   LIKE ITEM.Weight
  FIELD OnHand   LIKE ITEM.OnHand
  FIELD OnOrder  LIKE ITEM.OnOrder
  INDEX ItemNum IS UNIQUE ItemNum.

Run the procedure again and the duplicate records are gone from the ttItem table. The Items now come out in order as well, because of the index. This is the end of the display, as shown in the following figure.

Figure 5. TEMP-TABLE index enforces uniqueness


Tip: If there is a chance that a FILL operation might create duplicate records in a temp-table that you want eliminated, you must either define or inherit from the database schema a unique index for the temp-table, as done here for the ttItem table.