1 Introduction
The following figure shows one master with three detail sets linked to it.
Figure 3 Multiple Two-Level IMAGE Structure
Threads defined in the above IMAGE Structure
{A} or {B} or {C} or {D}
{A,B} or {B,A} {A,C} or {C,A}
{B,A,C} or {C,A,B}
Notice that detail data set D has two data paths to the same master. In this case, linking set A to set D is ambiguous. To resolve this ambiguity, it is necessary to specify which path is involved. Adding this capability to the thread specification allows the description of the following additional threads:
Additional Threads
{A (via path 1) D (via path 2) A}
{A (via path 2) D (via path 1) A}
{D (via path 1) A (via path 2) D}
{D (via path 2) A (via path 1) D}
{C,A (via path 1) D (via path 2) A,B}
{B,A (via path 2) D (via path 1) A,C}
etc.
Remember that although all these threads can be defined, they may not make any sense! It is the programmer's responsibility to determine the sense of a thread.
For another example, see the three reports on page 1-2. Generating report 2 involves using two sets. The thread that describes this hierarchy is specified as a list of PRODUCT and CUSTOMER. Report 3 involves three sets (PRODUCT, CUSTOMER and OPTION). The structures involved in all these reports are hierarchical in nature. In report 2, for example, the PRODUCT data set is higher in the hierarchy than CUSTOMER. Report 3 is an example of a three-level hierarchy. The next figure shows how the hierarchy for report 3 is organized.
Figure 4 Sample Three-level Hierarchy
Unlike report 2, where there is a direct connection between PRODUCT and CUSTOMER, there is no connection between CUSTOMER and OPTION. This is why the ORDER master data set exists. The thread necessary for accessing this three-level hierarchy consists of four sets which are specified in the order PRODUCT, CUSTOMER, ORDER and OPTION. See the next figure.
Figure 5 Simulation of a Three-level Hierarchy
A sample output for report 3 is shown next. Notice that information is obtained from the product data set (product number and description), as well as from each of the other sets. Graphically, this information is organized as shown on page 1-8. The numbers in the corner of the boxes correspond to the records where the information is stored in the database. Entries for the ORDER detail are not shown, since the ORDER set contains no information relevant to producing the report.
OUTSTANDING ORDERS LIST
PRODUCT NO. ORDER NO. CUSTOMER NAME OPTIONS PRICE
100(STD BICYCLE) 17,3 XYZ Company A 10,25
B 20,31
30,56
18,4 XYZ Company C 30,97
30,97
TOTAL 100 ORDERS: 61,53
500(5-SPEED) 19,1 ABC Company E 132,05
F 100,10
Q 1,23
224,38
TOTAL 500 ORDERS: 224,38
TOTAL ORDERS: 285,91
To produce report 3, it is necessary to extract this information from the database (record numbers from the figure titled "Sample Three-level Hierarchy" .)
| Set Name | Record to Read | Action to Take |
|---|---|---|
| Product | 5 | Print header product. |
| Customer | 5 | Print header for order. |
| Option | 1 | Print first option. |
| Option | 2 | Print last option and total. |
| Customer | 7 | Print header for new order. |
| Option | 4 | Print option and totals. |
| Product | 10 | Print header for new product. |
| Customer | 8 | Print header for order. |
| Option | 3 | Print first option. |
| Option | 6 | Print second option. |
| Option | 5 | Print last option and totals. |
The numbers stored in the workfile, however, always contain one record from each set. Thus, the first record will contain the three order number pointers and the pointer to the ORDER set.
The subsequent record is the same except that the pointer for the option set is changed to 2. The next figure shows the pointers as they are stored in the workfile.
Figure 6 Contents of Workfile after Sorting
Note that one pointer for each set is always stored. If a record at one level of the hierarchy has no records associated with it at the next lower level, there is no way to store a record of pointers in the workfile relevant to that record. In particular, if the records surrounded by a box in the figure titled, "Sample Three-level Hierarchy" are deleted, product 500 has no order associated with it and order 18,4 has no associated options. The workfile would then have only two records corresponding to the bracketed records in the next figure. Further, if the options on order number 17,3 were deleted, FIND or SORT would return an empty workfile.
The program to produce the outstanding order list is fairly complex, as shown in Chapter 3. However, the skeleton for the program is shown next. This skeleton reads four pointers from the workfile even though the third pointer (to the automatic master set ORDER) is not used. Also, note that this skeleton repeatedly reads records from the PRODUCT and CUSTOMER data set even though it may be reading the same record as on the previous pass through the loop. For clarity's sake, the code to optimize out the extra reads is not shown.
ASSIGN "XYZ" TO #1
WORKFILE IS #1;THREAD IS "PRODUCT","CUSTOMER","ORDER","OPTION"
.
.
.
IN DATA SET "CUSTOMER" USE ALL
IN DATA SET "OPTION" USE SKP 1,Option_desc$,PO
.
.
.
SORT BY Product_no, Order_no$,Option_desc$
.
.
.
FOR L=1 TO WFLEN(1)
READ #1;R1,R2,R3,R4
DBGET (Base$,"PRODUCT",4,S(*),"@",Buf$,R1)
DBGET (Base$,"CUSTOMER",4,S(*),"@",Buf$,R2)
DBGET (Base$,"OPTION",4,S(*),"@",Buf$,R4)
.
.
.
NEXT L