Filter
Top Products
ISQL and Tools
3-8 FairCom Corporation
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.