Using an Array
Often, the program must process all of the records before it can begin to print the data. During processing, the program must keep the data in a buffer where it can accumulate the numbers. This can be done in an SQR array.
An array is a unit of storage that contains rows and columns. An array is similar to a database table, but it exists only in memory.
The sample program specifies an array called order_qty to hold the sum of the quantity of orders in a given month. You could program this specific example without an array, but using one can be beneficial. Data that you retrieve once and store in an array can be presented in many ways without additional database queries. The data can even be presented in a chart.
The sample program also demonstrates an SQR feature called a three-dimensional array. This type of array has fields (columns) and rows, and it also has repeating fields (the third dimension). In the order_qty array, the first field is the product description. The second field is the order quantity of each month. The example includes three months; therefore, this field repeats three times.
SQR references arrays
in expressions such as array_name.field(sub1[,sub2]). The first subscript, sub1, is the row
number. The row count starts with zero. The second subscript, sub2, is specified
when the field repeats. Repeating fields are also numbered starting
with zero. The subscript can be a literal or an SQR numeric variable.
program ex8a.sqr
#define max_products 100
begin-setup
create-array
name=order_qty size={max_products}
field=product:char field=month_qty:number:3
end-setup
begin-program
do select_data
do print_array
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 #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_array
begin-procedure select_data
begin-select
order_date
! The quantity for this order
quantity
! 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
let #j = to_number(datetostr(&order_date,'MM')) - 1
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 4
print $current-date (1,1)
print 'Order Quantity by Product by Month' (1,18)
page-number (1,64) 'Page '
print 'Product' (3,1)
print ' January' (,32)
print ' February' (,42)
print ' March' (,52)
print ' Total' (,62)
print '-' (4,1,70) Fill
end-headingThe following output is for program ex8a.sqr:
11-JUN-04 Order Quantity by Product by Month Page 1
Product 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
Totals 60 82 150 292