ISQL and Tools
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 calcu- late 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 3-4: 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 an SQL aggregate function (AVG, MIN, MAX, SUM, or COUNT), a column name, a variable name, and a break specification. ISQL applies the aggre- gate function to all values of the column for the set of rows that corresponds to the break spec- ification. 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.
FairCom Corporation |
