|
|
|
Now that the query displays the rows it returns in a more acceptable format, you can use DISPLAY, COMPUTE, and BREAK statements to present order summaries for each customer.
All three statements rely on a break specification to indicate to ISQL when it should perform associated processing. There are four types of breaks you can specify:
While DISPLAY and COMPUTE statements specify what actions ISQL takes for a particular type of break, the BREAK statement itself controls which type of break is currently in effect. A consequence of this behavior is that DISPLAY and COMPUTE statements don’t take effect until you issue the BREAK statement with the corresponding break specification.
Also, keep in mind that there can be only one type of break in effect at a time. This means you can format a particular query for a single type of break.
In our example, we are interested in a column break, since we want to display an order summary for each customer. In particular, we want to display the name of the customer along with the number and total value of orders for that customer. And, we want this summary displayed whenever the value in the CUSTOMER_NAME column changes. In other words, we need to specify a column break on the CUSTOMER_NAME column.
Approach this task in two steps. First, devise a DISPLAY statement to display the customer name and confirm that it is displaying correctly. Then, issue COMPUTE statements to calculate the statistics for each customer (namely, the count and sum of orders), and add DISPLAY statement to include those statistics. All of the DISPLAY, COMPUTE and BREAK statements have to specify the same break to get the desired results.
The following example shows the DISPLAY and BREAK statements that display the customer name. The COL clause in the DISPLAY statement indents the display slightly to emphasize the change in presentation.
The following example uses the column formatting from previous examples. Notice that the column formatting also affects DISPLAY statements that specify the same column.
Example Specifying Column Breaks and Values with DISPLAY
ISQL> display col 5 "Summary of activity for", customer_name on customer_name;
ISQL> break on customer_name
ISQL> select c.customer_name, c.customer_city, o.order_id, o.order_value
from customers c, orders o
where o.customer_id = c.customer_id
order by c.customer_name;
CUSTOMER_NAME CUSTOMER_CITY ORDER_ID ORDER_VALUE
------------- ------------- -------- -----------
Aerospace Enterpris Scottsdale 13 $3,000,000.00
Aerospace Enterpris Scottsdale 14 $1,500,000.00
Summary of activity for Aerospace Enterpris
Chemical Constructi Joplin 11 $3,000,000.00
Chemical Constructi Joplin 12 $7,500,000.00
Summary of activity for Chemical Constructi
Luxury Cars Inc. North Ridgeville 21 $6,000,000.00
Luxury Cars Inc. North Ridgeville 20 $5,000,000.00
Summary of activity for Luxury Cars Inc.
.
.
.
Next, issue two COMPUTE statements to calculate the desired summary values.
COMPUTE statements specify a c-treeSQL aggregate function (AVG, MIN, MAX, SUM, or COUNT), a column name, a variable name, and a break specification. ISQL applies the aggregate function to all values of the column for the set of rows that corresponds to the break specification. It stores the result in the variable, which subsequent DISPLAY statements can use to display the result.
For this example, you need two separate compute statements. One calculates the number of orders (COUNT OF the ORDER_ID column) and the other calculates the total cost of orders (SUM OF the ORDER_VALUE column). Both specify the same break, namely, CUSTOMER_NAME. The following example shows the COMPUTE statements, which store the resulting value in the variables num_orders and tot_value.
The following example also issues two more DISPLAY statements to display the variable values. As before, the DISPLAY statements must specify the CUSTOMER_NAME break. They also indent their display further to indicate the relationship with the previously issued DISPLAY.
As before, this example uses the COLUMN and DISPLAY statements from previous examples. ISQL processes DISPLAY statements in the order they were issued. Use the DISPLAY statement, without any arguments, to show the current set of DISPLAY statements in effect. Also, in the query results, notice that the column formatting specified for the ORDER_VALUE column carries over to the tot_value variable, which is based on ORDER_VALUE.
Example Calculating Statistics on Column Breaks with COMPUTE
ISQL> compute count of order_id in num_orders on customer_name
ISQL> compute sum of order_value in tot_value on customer_name
ISQL> display col 10 "Total number of orders:", num_orders on customer_name;
ISQL> display col 10 "Total value of orders:", tot_value on customer_name;
ISQL> display -- See all the DISPLAY statements currently active:
display col 5 "Summary of activity for" ,customer_name on customer_name
display col 10 "Total number of orders:" ,num_orders on customer_name
display col 10 "Total value of orders:" ,tot_value on customer_name
ISQL> select c.customer_name, c.customer_city, o.order_id, o.order_value
from customers c, orders o
where o.customer_id = c.customer_id
order by c.customer_name;
CUSTOMER_NAME CUSTOMER_CITY ORDER_ID ORDER_VALUE
------------- ------------- -------- -----------
Aerospace Enterpris Scottsdale 13 $3,000,000.00
Aerospace Enterpris Scottsdale 14 $1,500,000.00
Summary of activity for Aerospace Enterpris
Total number of orders: 2
Total value of orders: $4,500,000.00
Chemical Constructi Joplin 11 $3,000,000.00
Chemical Constructi Joplin 12 $7,500,000.00
Summary of activity for Chemical Constructi
Total number of orders: 2
Total value of orders: $10,500,000.00
Luxury Cars Inc. North Ridgeville 21 $6,000,000.00
Luxury Cars Inc. North Ridgeville 20 $5,000,000.00
Summary of activity for Luxury Cars Inc.
Total number of orders: 2
Total value of orders: $11,000,000.00
.
.
.
