Using Multiple Arrays
Using SQR arrays to buffer data offers several advantages. In the previous example, it eliminated the need to sort the data. Another advantage is that you can combine the two sample reports into one. With one pass on the data, you can fill the two arrays and then print the two parts of the report.
The following sample program performs the work that is done by the first two programs. The SETUP section specifies two arrays: one to summarize monthly orders by product and another to summarize monthly orders by price range.
Program ex8c.sqr
#define max_categories 3
#define max_products 100
begin-setup
create-array
name=order_qty size={max_products}
field=product:char field=month_qty:number:3
create-array
name=order_qty2 size={max_categories}
field=category:char field=month_qty:number:3
end-setup
begin-program
do select_data
do print_array
print '-' (+2,1,70) fill
position (+1)
do print_array2
end-program
begin-procedure print_array
let #entry_cnt = #i
let #i = 0
while #i <= #entry_cnt
let $product = order_qty.product(#i)
let #jan = order_qty.month_qty(#i,0)
let #feb = order_qty.month_qty(#i,1)
let #mar = order_qty.month_qty(#i,2)
let #prod_tot = #jan + #feb + #mar
print $product (,1,30)
print #jan (,32,9) edit 9,999,999
print #feb (,42,9) edit 9,999,999
print #mar (,52,9) edit 9,999,999
print #prod_tot (,62,9) edit 9,999,999
position (+1)
let #i = #i + 1
end-while
end-procedure ! print_array
begin-procedure print_array2
let #i = 0
while #i < {max_categories}
let $category = order_qty2.category(#i)
let #jan = order_qty2.month_qty(#i,0)
let #feb = order_qty2.month_qty(#i,1)
let #mar = order_qty2.month_qty(#i,2)
let #category_tot = #jan + #feb + #mar
print $category (,1,31)
print #jan (,32,9) edit 9,999,999
print #feb (,42,9) edit 9,999,999
print #mar (,52,9) edit 9,999,999
print #category_tot (,62,9) edit 9,999,999
position (+1)
let #jan_total = #jan_total + #jan
let #feb_total = #feb_total + #feb
let #mar_total = #mar_total + #mar
let #i = #i + 1
end-while
let #grand_total = #jan_total + #feb_total + #mar_total
print 'Totals' (+2,1)
print #jan_total (,32,9) edit 9,999,999
print #feb_total (,42,9) edit 9,999,999
print #mar_total (,52,9) edit 9,999,999
print #grand_total (,62,9) edit 9,999,999
end-procedure ! print_array2
begin-procedure select_data
let order_qty2.category(0)='$0-$4.99'
let order_qty2.category(1)='$5.00-$100.00'
let order_qty2.category(2)='Over $100'
begin-select
order_date
! the price / price category for the order
c.price &price
move &price to #price_num
evaluate #price_num
when < 5.0
let #x = 0
break
when <= 100.0
let #x = 1
break
when-other
let #x = 2
break
end-evaluate
! The quantity for this order
quantity
let #j = to_number(datetostr(&order_date,'MM')) - 1
if #j < 3
let order_qty2.month_qty(#x,#j) =
order_qty2.month_qty(#x,#j) + &quantity
end-if
! the product for this order
description
if #i = 0 and order_qty.product(#i) = ''
let order_qty.product(#i) = &description
end-if
if order_qty.product(#i) != &description
let #i = #i + 1
if #i >= {max_products}
display 'Error: There are more than {max_products} products'
stop
end-if
let order_qty.product(#i) = &description
end-if
if #j < 3
let order_qty.month_qty(#i,#j) =
order_qty.month_qty(#i,#j) + &quantity
end-if
from orders a, ordlines b, products c
where a.order_num = b.order_num
and b.product_code = c.product_code
order by description
end-select
end-procedure ! select_data
begin-heading 5
print $current-date (1,1)
page-number (1,64) 'Page '
print 'Order Quantity by Product and Price Category by Month' (2,10)
print 'Product / Price Category' (4,1)
print ' January' (,32)
print ' February' (,42)
print ' March' (,52)
print ' Total' (,62)
print '-' (5,1,70) Fill
end-heading
The following is the output for program ex8c.sqr:
11-JUN-04 Page 1
Order Quantity by Product and Price Category by Month
Product / Price Category January February March Total
----------------------------------------------------------------------
Canisters 3 0 0 3
Curtain rods 2 8 18 28
Ginger snaps 1 10 0 11
Hanging plants 1 20 0 21
Hookup wire 16 15 0 31
Hop scotch kits 2 0 0 2
Modeling clay 5 0 0 5
New car 1 9 0 10
Thimble 7 20 0 27
Thingamajigs 17 0 120 137
Widgets 4 0 12 16
Wire rings 1 0 0 1
----------------------------------------------------------------------
0-4.99 28 45 12 85
5.00-100.00 25 28 138 191
Over 100 7 9 0 16Totals 60 82 150 292SQR arrays are also advantageous in programs that produce charts. With the data for the chart already in an array, presenting this cross-tabular report as a bar chart is easy.