Of course we can use arrays like tables.  What else can we do?

Use Arrays To Initialize Variables

An array can be much more than a table with different features and functionality.  It enables a variety of new algorithms.

The create-array and clear-array commands are easy ways to initialize and re-initialize the fields of an array.  The next step is to use an array to initialize and re-initialize scalar variables.

create-array name=zeroes size=1
  field=a0:number=0
  field=a1:number=0
  field=a2:number=0
  field=a3:number=0
  field=a4:number=0

get #num_emps #num_depts #num_supervisors #total_salary #total_experience from zeroes(0)
get #counter1 #counter2 #num_locations #num_customers #total_sales from zeroes(0)

Use Arrays To Group Variables

Arrays are also handy for grouping variables, establishing conceptual connections, and improving naming standards.

create-array name=sales_office size={num_sales_offices}
  field=name:char
  field=city:char
  field=state:char
  field=manager:char
  field=num_staff:integer
  field=sales_quota:number
  field=quota_percent_realized:number

create-array names=sales_staff size={num_sales_staff}
  field=name:char
  field=sales_office:char
  field=sales_quota:number
  field=actual_sales:number

It looks like a normalized database, doesn’t it?  We use field names “name” and “sales_quota” in two places.  Does that improve clarity or diminish it?  Did you guess that sales_staff.sales_office(#i) was a foreign key?

When the array size = 1, we’ve grouped a list of variables and indicated to the reader that they are connected.  When we use them in if, let, or while commands, the compiler requires us to use the array name as a prefix and requires us to spell it the same way every time.  If we use the array commands (add-array, put, get, etc.) with multiple values, the compiler requires us to work with these related variables together, without mixing in unrelated variables.

Use Arrays To Simplify SQL

A business object  or person (employee, customer, student) may be described by many database tables.  To gather all the information our SQR program needs, we’re tempted to join all those tables in a single selection.  If one of those tables doesn’t contain some people (new employees who haven’t enrolled in a benefit plan), an inner join will exclude them completely.  An outer join will include whatever data they have, but with a sacrifice of performance.

Arrays allow us to read each table individually, store the results, and have blanks (a single space) or null for the missing data.  This can even be faster than an inner join, to say nothing of an outer join.

Use Arrays To Simulate Parallel Selections

In SQR, it is difficult and inefficient to select two tables at once, reading the first row from the first table, then the first row from the second table, then second row from the first table, then the second row from the second table, and so on.

It is easy and efficient to read the first table into an array, and then move through the array while reading the second table.  This approach is good for comparing two tables for keys in one that are missing in the other.  It can also replace some “where exists” and “where not exists” clauses.

Use Arrays For Multilevel Subtotals

Some reports have elaborate hierarchies of subtotals, and SQR supports them with the on-break clause.  This wonderful feature can be used with individual columns of a begin-select command or with print commands (presumably fed by an array) within a while loop.  Alternately, we can test for changes in variables that we are about to print.

Either way, we can perform a block of code that prints and re-initializes subtotals.  In complex reports, we may have several separate procedures to print grand totals, totals, subtotals, sub-subtotals, etc.  Often the procedures are the same in form, but with different variable names and literal strings.  It becomes annoying to insert one more column at the detail level and at each total level.

Sometimes we can consolidate all these routines into one that uses arrays.

begin-procedure print_totals(#level, $emplid, $deptid, $business_unit, $company)
evaluate #level
  when = {employee_level}
    let $label = ‘Total for employee ‘ || $emplid
  when = {department_level}
    let $label = ‘Total for department ‘ || $deptid
  when = {business_unit_level}
    let $label = ‘Total for business unit ‘ || $business_unit
  when = {company_level}
    let $label = ‘Total for company ‘ || $company
end-evaluate
print $label (+2, 1)

get #num_emp #salary #budget from totals(#level)
print #num_emp ( , 20) edit 9999
print #salary  ( , 30) edit $999,999.99
print #budget  ( , 50) edit $999,999.99

let #next_level = #level + 1
array-add #num_emp #salary #budget to totals(#next_level)
put 0 0 0 into totals(#level)
end-procedure

Using Arrays To Reorder Output

Sometimes the most efficient way to generate information will produce it in a different order than we want to print it.  I wrote a report that compares personal and benefits data from a dozen Peoplesoft HCM tables.  First, it reads all the employees from all the tables into a single array (see “Use Arrays To Simply SQL” above.)  Next, it performs 150 comparisons for each employee, and stores any inconsistencies in an array of error messages.  Finally, it sorts and prints the error messages.

Using Arrays For Cross Tabulation Reports

A cross tabulation report looks like a spreadsheet in which each row is labelled, each column is labelled, and each number relates to its row and column.  For example, each row could be the name of a product, each column could be the name of a region.  Then each number could be the number of that product sold in that region or the dollar amount of sales for that product in that region.

It might be possible to group, order, and count or sum an SQL selection so that the first row has the value for the first position of the report, the second row has the second value, and so on.  However, that type of code will be inflexible for any changes in the business model or the report requirements.  It’s usually easier and more robust to create an array modelled after the report and fill it from one or more SQL selections, plus any other required calculations, modelled after the database structure.

Northern California Regional User Group

The user group for Peoplesoft Enterprise, Enterprise One (formerly J.D. Edwards), and World (formerly J.D. Edwards) is reorganizing.  Last May, I got an email from the national Quest User Group inviting me to help reactivate the group.  Since most Peoplesoft Enterprise customers work with SQR, we should participate.  If you, dear reader, live in Northern California, please contact me.  You can leave a comment or send me an email (steven@peoplesoftsqr.com).  I won’t publish your comment unless you authorize it, but I will keep you informed of the new user group’s developments.