HP 3000 Manuals HP 3000 Manuals
Sorting the Data
Let's change the report to be all elements except cust-no so that it will
fit on one line. Also, we will sort the report by city-state.
_______________________________________________________
| |
| 1 system ex2,base=orders; |
| 2 list name: |
| street-addr: |
| city-state: |
| zipcode; |
| 3 output(serial) customer,sort=(city-state);|
_______________________________________________________
Figure 1-4. Program to sort and report data
2 Since we do not want access to all items from the customer set,
we must individually list the items that we do want.
3 Appending the SORT= phrase to the OUTPUT verb tells Transact to
sort the data in ascending sequence by the city-state item.
By default, the OUTPUT verb retrieves and reports all items that
are LISTed to the program.
An alternative way to do the report is:
_______________________________________________________
| |
| 1 system ex2a,base=orders; |
| 2 list(auto) customer; |
| 3 format name: |
| street-addr: |
| city-state: |
| zipcode; |
| 4 output(serial) customer,sort=(city-state);|
_______________________________________________________
Figure 1-5. Program to sort data and use FORMAT for reporting
3 Rather than specify through the LIST verb that a subset of the
customer items is to be retrieved, all the items are retrieved,
but the FORMAT verb specifies to OUTPUT that only the named
subset is to be reported.
Either of these programs produces a report that looks like the one below:
_______________________________________________________________________________
| |
| NAME: STREET-ADDR: CITY-STATE: ZIPCODE:|
| Hold A Hill Planter 651 El Camino Dallas, Texas 45623 |
| Balcon's Bridal 1775 Capitol Express Fresno, Ca. 98167 |
| Hobie Cat 3410 Monterey Rd Gilroy, Ca. 96144 |
| Furtado Inports 1396 E Santa Clara Los Angeles, Ca. 90189 |
| Cobalt Boats 2250 San Ramon Louisville, Ky. 33246 |
| Frank Leary Racing 590 Laurelwood Mountain View, Ca. 92123 |
| Excl Chemical Co 630 Walsh New York, NY 44636 |
| Bay Repro 123 Hospital Dr Palo Alto, Ca. 94967 |
| Natkin & Co 807 Aldo Av Redwood City, Ca. 93144 |
| Drama Books 511 Geary St. Redwood City, Ca. 92143 |
| Victorian Antiques 476 S 1st San Francisco, Ca. 94123 |
| Able-1 Answering 2775 Park Av San Jose, Ca 95111 |
| Vinicator Corp 1092 Steward Drive San Jose, Ca 95144 |
| Bayliner Boats 1548 Maple San Jose, Ca. 95144 |
| Pour House 1475 Lipton Place San Jose, Ca. 95122 |
| Grand Depression 27 E Main Santa Clara, Ca 95122 |
| Saxon's 518 W San Carlos Santa Clara, Ca. 94168 |
| Rummage Palace 410 N 10th South Bend, Ind. 49146 |
| |
| 19 RECORDS FOUND |
| |
| EXIT/RESTART(E/R)?> |
_______________________________________________________________________________
Figure 1-6. The sorted report on customers
Formatting Options
The FORMAT verb also provides extensive reporting options for such things
as field editing, heading text, line breaks, column positioning, etc.
The following program illustrates a few of the options available on the
FORMAT verb.
____________________________________________________
| |
| 1 system ex2b,base=orders; |
| 2 list(auto) customer; |
| 3 format $today,edit="3w. 3m DD, YYYY": |
| 4 "LIST OF CUSTOMERS REPORT",col=30: |
| 5 "PAGE",col=60: |
| 6 $page: |
| 7 "CUSTOMER",line=2,title: |
| 8 name,nohead,line=2: |
| 9 street-addr,line,nohead: |
| 10 city-state,line,nohead: |
| 11 zipcode,nohead,join=1; |
| 12 output(serial) customer,sort=(city-state);|
____________________________________________________
Figure 1-7. Options for FORMAT
3 $TODAY is a special name that means print today's date. The edit
options specify to print the first three characters of the day of
the week followed by a period, then print the first three
characters of the month, then the numeric day of the month, and
last the year.
4 Any literal to be displayed is placed in quotation marks. The
COL= option indicates an absolute report column number for the
start of the display of an item. In the example the literal
value begins in column 30.
6 $PAGE is a special name that means print the current page number
here.
7 The LINE= option indicates the number of lines to advance before
displaying the item that the option is attached to. In the
example, the line count is advanced by two lines.
The TITLE option indicates that all report definitions that
precede this, including the current report item, make up a report
heading or title. These items appear at the top of each new
page.
8 The NOHEAD option specifies that no column heading is to be
generated for this item. We have decided to provide our own
column heading called CUSTOMER.
Note that the LINE= option is used here to indicate that the
report is to advance two lines before printing the customer name.
9 This line prints the value of the street address immediately
under the name and suppresses the generation of a column heading.
10 Line 10 prints rint the city and state immediately under the
street address and suppresses generation of a column heading.
11 The JOIN= option specifies that this item is to be joined to the
previous item by leaving only one blank space between the two
items. For example, the data item CITY-STATE is 20 bytes long.
It only takes 13 bytes to store the value "San Jose, Ca." . Thus
there are 7 trailing blanks in the CITY-STATE item for this
value. JOIN=1 specifies that only one blank should appear
between the last nonblank character of CITY-STATE and the first
character of ZIP-CODE.
The program produces a report that looks like this:
________________________________________________________________________
| |
| Wed. Mar 30, 1988 LIST OF CUSTOMERS REPORT PAGE 1|
| |
| CUSTOMER |
| |
| Able-1 Answering |
| 2775 Park Av |
| San Jose, Ca. 95111 |
| |
| Grand Depression |
| 27 E Main |
| Santa Clara, Ca. 95122 |
________________________________________________________________________
Figure 1-8. Report produced by FORMAT options
Selective Reporting
Now let's see some examples of selective reporting. The first program
below prints all orders for a particular customer.
_______________________________________
| |
| 1 system ex3,base=orders; |
| 2 list(auto) orderhead; |
| 3 data cust-no; |
| 4 set(match) list (cust-no);|
| 5 output(serial) orderhead; |
_______________________________________
Figure 1-9. Program to select data for reporting
3 The DATA verb is a data entry verb. When the program is run, the
user is prompted to enter the cust-no. By default, the prompt
uses the name of the element. This could be overridden here in
several ways. For example, we could define entry text in the
dictionary to be used when prompting for this field. We could
also specify prompt text as a part of the DATA verb.
4 SET(MATCH) sets up a match criterion so that a record is selected
only if the cust-no is equal to the one entered into the program
via line 3. This is the default. We could also specify other
match operators, such as LT (less than), GT (greater than), NE
(not equal), etc.
5 OUTPUT(SERIAL) specifies a serial read through the orderhead
dataset. When a cust-no matches the one entered by the user,
then the record is selected.
Here is an example of running the program listed in Figure 1-9.
____________________________________________________
| |
| CUST-NO> 1 |
| |
| ORDER-NO: CUST-NO: ORDER-STATUS: ORDER-DATE:|
| po1001 1 o 850101 |
| po1002 1 o 850203 |
| po1003 1 o 850127 |
| po1004 1 o 850301 |
| po1005 1 c 850212 |
| |
| 5 RECORDS FOUND |
| |
| EXIT/RESTART(E/R)?> |
____________________________________________________
Figure 1-10. Report of selected data
Setting up match criteria and serially reading datasets and files will
work against any kind of file. However, in the case of Image, we can
take advantage of search keys to retrieve the data more rapidly. Cust-no
happens to be a search key in the orderhead dataset. Thus we can
retrieve the data desired more rapidly if we change the program to the
following:
_____________________________________
| |
| 1 system ex4,base=orders; |
| 2 list(auto) orderhead; |
| 3 data cust-no; |
| 4 set(key) list (cust-no);|
| 5 output(chain) orderhead;|
_____________________________________
Figure 1-11. Program to select data by key value
4 The retrieval is to be by the dataset key cust-no. The value of
the cust-no is requested from the user in line 3.
5 The retrieval is to be done by Image CHAINed reads.
These examples show how a single input value can be used to qualify or
select data. However, with a slight change, we can put the data
selection logic into the hands of the user. Doing so allows a single
program to be used to select data on the basis of any number of selection
criteria entered by the user.
The following example is an expansion of the example in Figure 1-9.
______________________________________
| |
| 1 system ex4a,base=orders; |
| 2 list(auto) orderhead; |
| 3 data(match) cust-no: |
| order-status:|
| order-date; |
| 4 output(serial) orderhead;|
______________________________________
Figure 1-12. Program to let user set selection criteria
We have combined lines 3 and 4 into a single DATA(MATCH) verb. This verb
not only provides data entry, but also recognizes a variety of relational
conditions that can be provided by the user at run time. These
relational conditions are used as the selection criteria for data
retrieval.
The following examples demonstrate how to run the program and enter
various relational conditions.
___________________________________________________
| |
| CUST-NO> 1,2 |
| |
| ORDER-STATUS> |
| |
| ORDER-DATE> |
| |
| ORDER-NO: CUST-NO: ORDER-STATUS: ORDER-DATE:|
| po1001 1 o 850101 |
| po1002 1 o 850203 |
| po1003 1 0 850127 |
| po1004 1 0 850301 |
| po1005 1 c 850212 |
| po2001 2 h 880301 |
| po2002 2 o 880312 |
___________________________________________________
Figure 1-13. User-entered selection criteria
In this example, the user specified that cust-no 1 and 2 are to be
retrieved. If data values are provided without any relational
conditions, equality is assumed. That is, retrieve the data if it
matches any of the data values provided in the list.
No relational conditions were provided for items order-status or
order-date. Thus, no data is excluded based on these two items.
___________________________________________________
| |
| CUST-NO> > 0 and < 2 |
| |
| ORDER-STATUS> <> c,h |
| |
| ORDER-DATE> 85^^ |
| |
| ORDER-NO: CUST-NO: ORDER-STATUS: ORDER-DATE:|
| po1001 1 o 850101 |
| po1002 1 o 850203 |
| po1003 1 o 850127 |
| po1004 1 o 850301 |
___________________________________________________
Figure 1-14. More user-entered selection criteria
In this example, the relational conditions input by the user specify that
order data is to be retrieved if the cust-no is greater than 0 and less
than 2 and order-status is not equal to "c" or "h" and order-date begins
with 85.