HP 3000 Manuals HP 3000 Manuals
Reporting from Multiple Datasets
The following program prints the customer's name rather than the
customer's cust-no. This requires data to be retrieved from both the
customer set and the orderhead set.
______________________________________________
| |
| 1 system ex5,base=orders; |
| 2 list(auto) customer; |
| 3 list(auto) orderhead; |
| 4 data cust-no; |
| 5 set(key) list (cust-no); |
| 6 get customer,list=(@); |
| 7 format name: |
| order-no: |
| order-status; |
| 8 output(chain) orderhead,list=(@);|
______________________________________________
Figure 1-15. Program to report from two datasets
2 LIST(AUTO) reserves space to hold records from the customer set.
3 LIST(AUTO) reserves space to hold records from the orderhead set.
4 DATA asks the user to input the value of the cust-no to be
retrieved.
5 SET(KEY) specifies that cust-no is a key element. For Image,
this establishes the value to be used for direct retrieval from a
master dataset and chained retrieval from a detail dataset.
6 GET CUSTOMER gets the customer record. The LIST=(@) option
specifies that we retrieve the entire record. This is equivalent
to specifying
list=(cust-no,name,street-addr,city-state,zipcode);
7 The default option for the OUTPUT verb is to print all data items
that we have listed. To limit our printing, we must use the
FORMAT verb first to indicate which data items we want to print.
8 Since the program is accessing data from more than one dataset,
we must specify the subset of data that is to be retrieved by
OUTPUT. The LIST=(@) limits data retrieval to the elements in the
orderhead dataset.
Figure 1-16 shows the report produced by this program.
____________________________________________________
| |
| CUST-NO> 1 |
| |
| NAME: ORDER-NO: ORDER-STATUS:|
| Able-1 Answering po1001 o |
| Able-1 Answering po1002 o |
| Able-1 Answering po1003 o |
| Able-1 Answering po1004 o |
| Able-1 Answering po1005 c |
| |
| 5 RECORDS FOUND |
| |
| EXIT/RESTART(E/R)?> |
____________________________________________________
Figure 1-16. Report from two datasets
Now let's create a program to retrieve all orders that are open, i.e.,
all orders that have order-status equal to "o" . Also, let's print out
the details of each order. The operation requires the use of a master
and two detail sets: customer, orderhead, and orderline.
___________________________________________________________
| |
| 1 system ex6,base=orders; |
| 2 list(auto) orderhead; |
| 3 move (order-status) = "o"; |
| 4 set(match) list (order-status); |
| 5 find(serial) orderhead,list=(@) |
| ,perform=get-orderdata;|
| 6 exit; |
| |
| 7 get-orderdata: |
| |
| 8 level; |
| 9 set(key) list (cust-no); |
| 10 list(auto) customer; |
| 11 get customer,list=(@); |
| 12 set(key) list (order-no); |
| 13 list(auto) orderline; |
| 14 format name: |
| order-no: |
| line-no: |
| part-number: |
| quantity; |
| 15 output(chain) orderline,list=(@) |
| ,nocount; |
| 16 end(level); |
| 17 return; |
___________________________________________________________
Figure 1-17. Program to report from three datasets
3 MOVE sets up the match value that we want for selection. We want
to select open orders only.
4 SET(MATCH) establishes the match criterion for the data retrieval
verb FIND (in line 5). The match element is order-status and the
match value is "o" .
5 The FIND verb retrieves all records from the orderhead set that
match the selection criterion of being an open order. The
LIST=(@) specifies that all data elements in the orderhead
dataset are to be retrieved (for each record) that is selected.
For each record that is retrieved, control is transferred to the
label get-orderdata as specified by the PERFORM= option.
8 We use LEVEL to take advantage of some automatic housekeeping
provided by Transact. This statement and the END(LEVEL)
statement on line 16 specify a program area where data elements
retrieved from data sets are temporarily stored. When the level
is ended, the storage area for the elements is released. LEVELs
will be discussed more in later sections.
9 Lines 9-11 setup and retrieve the customer record for the
customer number just retrieved from orderhead.
12 SET(KEY) sets up the retrieval key for order details.
13 LIST(AUTO) reserves the space to hold the orderline record.
14 FORMAT sets up the list of data items that we want to report via
output. The name comes from the customer dataset. All other
items come from orderline.
15 OUTPUT(CHAIN) retrieves the order details and prints the data.
The NOCOUNT option suppresses printing the number of records
found.
16 END(LEVEL) releases the storage space for customer and orderline
records.
17 RETURN specifies that control is to be returned to the statement
that contains the PERFORM instruction. This is the FIND verb in
statement 5.
This program produces the report shown below. Since the OUTPUT verb
controls retrieval of detail information for each order, the report is
formatted with headings for each order number.
_____________________________________________________________________________
| |
| NAME: ORDER-NO: LINE-NO: PART-NUMBER: QUANTITY: :|
| Able-1 Answering po1001 10 p121 75 |
| Able-1 Answering po1001 20 p123 150 |
| |
| NAME: ORDER-NO: LINE-NO: PART-NUMBER: QUANTITY: :|
| Able-1 Answering po1002 10 p122 50 |
| Able-1 Answering po1002 20 p124 10 |
| Able-1 Answering po1002 30 p127 243 |
| |
| NAME: ORDER-NO: LINE-NO: PART-NUMBER: QUANTITY: :|
| Able-1 Answering po1003 10 p121 10 |
| |
| NAME: ORDER-NO: LINE-NO: PART-NUMBER: QUANTITY: :|
| Able-1 Answering po1004 10 p122 20 |
| |
| NAME: ORDER-NO: LINE-NO: PART-NUMBER: QUANTITY: :|
| Grand Depression po2003 10 p126 50 |
| |
| END OF PROGRAM |
| : |
_____________________________________________________________________________
Figure 1-18. Report from three datasets
If we want to see the same data in a more typical report format, then we
can use the other reporting verb in Transact, which is the DISPLAY verb.
The following program demonstrates how the report above can be generated
using this verb.
____________________________________________________________
| |
| 1 system ex7,base=orders; |
| 2 list(auto) orderhead; |
| 3 move (order-status) = "o"; |
| 4 set(match) list (order-status); |
| 5 find(serial) orderhead,list=(@) |
| ,perform=get-orderdata;|
| 6 exit; |
| |
| 7 get-orderdata: |
| |
| 8 level; |
| 9 set(key) list (cust-no); |
| 10 list(auto) customer; |
| 11 get customer,list=(@); |
| 12 set(key) list (order-no); |
| 13 list(auto) orderline; |
| 14 find(chain) orderline,list=(@) |
| ,perform=displayit; |
| 15 end(level); |
| 16 return; |
| |
| 17 displayit: |
| |
| 18 display(table) name: |
| order-no: |
| line-no: |
| part-number: |
| quantity; |
| 19 return; |
____________________________________________________________
Figure 1-19. Program to create a report with DISPLAY(TABLE)
14 Replace the FORMAT and OUTPUT verbs with a FIND verb which
performs a routine to display the data.
18 DISPLAY displays the information. The TABLE option specifies
that we want Transact to print the headings at the start of each
new page.
The items to be reported are specified as a part of the DISPLAY
verb.
Our report now looks like this:
______________________________________________________________________
| |
| NAME: ORDER-NO: LINE-NO: PART-NUMBER: QUANTITY:|
| Able-1 Answering po1001 10 p121 75 |
| Able-1 Answering po1001 20 p123 150 |
| Able-1 Answering po1002 10 p122 50 |
| Able-1 Answering po1002 20 p124 10 |
| Able-1 Answering po1002 30 p127 243 |
| Able-1 Answering po1003 10 p121 10 |
| Able-1 Answering po1004 10 p122 20 |
| Grand Depression po2003 10 p126 50 |
| |
| END OF PROGRAM |
| : |
______________________________________________________________________
Figure 1-20. Report created by DISPLAY(TABLE)
Data selection can also be specified programmatically by using Transact's
IF verb. This verb is very similar in functionality to the IF verb in
languages such as COBOL and Pascal.
The following program illustrates how IF can be used to select a report
of order details by order line, if the order line generates sales of over
$1000. This program brings into play a fourth dataset, PARTS. It also
changes the order in which data is retrieved.
_______________________________________________________________
| |
| 1 system ex7a,base=orders; |
| 2 list(auto) orderhead; |
| 3 move (order-status) = "o"; |
| 4 set(match) list (order-status); |
| 5 find(serial) orderhead,list=(@) |
| ,perform=get-orderdata; |
| 6 exit; |
| |
| 7 get-orderdata: |
| |
| 8 level; |
| 9 set(key) list (order-no); |
| 10 list(auto) orderline; |
| 11 find(chain) orderline,list=(@) |
| ,perform=select-orderline;|
| 12 end(level); |
| 13 return; |
| |
| 14 select-orderline: |
| |
| 15 level; |
| 16 set(key) list (part-number); |
| 17 list(auto) parts; |
| 18 get parts,list=(@); |
| 19 if [(quantity) * (selling-price)] > 1000 then |
| 20 do |
| 21 set(key) list (cust-no); |
| 22 list(auto) customer; |
| 23 get customer,list=(@); |
| 24 display(table) name: |
| order-no: |
| line-no: |
| part-number: |
| quantity: |
| selling-price; |
| 25 doend; |
| 26 end(level); |
| 27 return; |
_______________________________________________________________
Figure 1-21. Program to select data by the conditional verb IF
9 Lines 9-11 get the order information from orders before
retrieving the customer information.
16 Lines 16-18 set up and retrieve the parts record for the current
part number.
19 A further screening is done on the record.
20 If the record passes the screening in line 19, then lines 20-25
are performed.
21 Lines 21-23 get the customer name.
An example of the report follows:
____________________________________________________________________________________
| |
| NAME: ORDER-NO: LINE-NO: PART-NUMBER: QUANTITY: SELLING-PRICE:|
| Able-1 Answering p01001 20 p123 150 20.00 |
| Able-1 Answering p01002 10 p122 50 21.00 |
| Able-1 Answering p01002 30 p127 243 50.00 |
| Grand Depression p02003 10 p126 50 40.00 |
____________________________________________________________________________________
Figure 1-22. Data selected with conditional verb IF