Creating an Array

You must define the size of an array when you create it. The sample program creates the order_qty array with a size of 100.

The #DEFINE MAX_PRODUCTS 100 command defines the max_products constant as a substitution variable. The sample program uses this constant to define the size of the array. Using #DEFINE is a good practice because it displays the limit at the top of the program source. Otherwise, it would be hidden in the code.

The SETUP section creates the array by using the CREATE-ARRAYcommand. All SQR arrays are created before the program begins running. Their size must be known at compile time. If you do not know exactly how many rows you have, you must over-allocate and specify an upper bound. In the example, the array has 100 rows even though the program uses only 12 rows to process the sample data.

The preceding program has two procedures: select_data and print_array. Select_data performs the database query, as its name suggests. While the database records are being processed, no data prints and the data accumulates in the array. When the processing is complete, the print_array procedure does two things: the procedure loops through the array and prints the data, and it also adds the month totals and prints them at the bottom.

The report summarizes the product order quantities for each month, which are the records ordered by the product description. The procedure then fills the array one product at a time. For each record that is selected, the procedure checks to see whether it is a new product; if it is, the array is incremented by row subscript #i. The procedure also adds the quantity to the corresponding entry in the array based on the month.

This program has one complication: how to obtain the month. Date manipulation can vary among databases, and to write truly portable code requires careful planning.

The key is the datetostr function in the following command:

let #j = to_number(datetostr(&order_date, 'MM')) - 1

This function converts the order_date column into a string. (The ‘MM’ edit mask specifies that only the month part be converted.) The resulting string is then converted to a number; if it is less than 3, it represents January, February, or March and is added to the array.