June 13, 2005

SQR Arrays

Some SQR programmers use arrays extensively while others rarely if ever use them. If you're like me, the first time you saw arrays in an SQR program, you were probably wondering how they came up with THAT syntax! SQR arrays are a little different from those in other languages. But they do offer a good deal of flexibility.

Arrays can be either one-dimensional, two-dimensional, or something like what other languages would call arrays of "records" or user-defined types. One array can even be a combination of these--for example, several two-dimensional arrays.

Arrays are defined with the CREATE-ARRAY statement, which defines the size (number of rows) and columns ("fields") in the array.

   create-array
     name=CustomerBalances
     size=1000
     field=CustomerID:char
     field=Name:char
     field=Balance:number

This array has up to 1000 rows (numbered from 0 to 999), and 3 columns containing dissimilar types of data. (Note that all arrays are global in scope and are created before the program begins to run. Therefore, CREATE-ARRAY cannot be bypassed by an IF, for example.)

There are also two completely different ways of referencing array elements, although the first seems to be the most common:

   put '001' 'John Doe' 123.45
     into CustomerBalances(0) CustomerID Name Balance

   ! Or use this syntax:

   let CustomerBalances.CustomerID(0) = '001'
   let CustomerBalances.Name(0) = 'John Doe'
   let CustomerBalances.Balance(0) = 123.45

We can then read the array data using either of these types of syntax:

   get $CustID $Name #Balance
     from CustomerBalances(0) CustomerID Name Balance

   ! Or use this syntax:

   let $CustID = CustomerBalances.CustomerID(0)
   if CustomerBalances.Balance(0) > 0
     ...

The first type of syntax (GET/PUT) seems to be used more often in PeopleSoft programs and is actually sort of a shortcut. The other type of syntax lets us use array elements directly if we want without moving them to or from regular variables.

We can build two-dimensional arrays by specifying a number of occurrences for a field. For example, if we wanted to store balances by month:

   create-array
     name=CustomerBalances
     size=1000
     field=CustomerID:char
     field=Name:char
     field=Balance:number:12

The only change here is the addition of the "12" for the Balance field. This gives us 12 Balance fields on each row of the array. The first balance for the first customer is then available as CustomerBalances.Balance(0,0):

   let CustomerBalances.Balance(0,0) = 100
   let CustomerBalances.Balance(0,1) = 200

Using this example and after filling some of the fields with data, here is how this array can be visualized (as shown in SP Debugger for SQR):


Although they may seem strange at first, SQR arrays give us a lot of flexibility and can be used to simplify our programs or improve their performance.

Until next time...