Hooray For Arrays.Part(2)

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.

9 Comments

  1. Bob Josephson says:

    Maybe you should post the User Group info as a separate article, so people who don’t read about arrays will still see it.

    Alas, I don’t live in Northern California. In fact, I’m not sure I even have a life outside of computers.
     .   .
       |
    ~~~

  2. Anand says:

    Hi all..

    I tried with concept of Array.. My table consist of more than 500 rows.. I tried with minimum value to size of the array says 100.. when i was trying to retrieve the value from table to array.. It didnt retrieve any value from table.. And it doesnt show any error message also.. After I increased the size of the array. Its retrieves the value from table.. why its so.. I hope the sql statement runs beyond the size of the array.. even it is overflow r underflow atleast it should retrieve minimum records as i mentioned in size.. I want to know when overflow occurs whether the entire array is cleared.. (I am using brio 6.1 and SQR for oracle. extent is not there to increase the size of the array automatically)

    Thanks in Advance
    Anandhraj

    • administrator says:

      Hi Anand,

      You read each row from the database table and store some values in the array. You increment the array index either before or after each row. Then you look at values in the array, with an index value that you previously used to store values, and you don’t find anything. That sounds strange. If you use an index value less than zero or greater than 99, you should get a fatal error message that stops the program; it won’t clear the array and continue. Please send me a copy of the code that doesn’t work.

  3. Anand says:

    Hi,

    This is the program I tried for the Array.. test_emp table contain 14 records but but size of the array is 10.

    !TO check Array

    begin-setup
    declare-variable
    default-numeric=integer
    end-declare
    end-setup

    begin-program
    let #j=0
    create-array name=emparr size=10
    field=eno:number
    field=empname:char
    field=sall:number

    begin-select
    empno
    ename
    sal
    put &empno &ename &sal into emparr(#j)
    let #j=#j+1
    from test_emp
    end-select

    while #i < #j
    get #eno $empname #sall from emparr(#i)
    print #eno (+1,5)
    print $empname (,15)
    print #sall (,25)
    let #i=#i+1
    end-while
    end-program

    Initially I thought that error would be in #j since it is decimal so i made default numeric in begin-setup. After that also it doesnt work, and i tried increasing the size of the array to 14 or more then it works fine…

    • administrator says:

      Have you read your log file carefully? You may have an error message when the begin-select loop tries to put into emparr(10), which would stop the program before it executes any print commands.

  4. Anand says:

    No it doesnt show any error in log.. instead i tried, what u said by selecting the value from table put it in array. and checked whether value is available.. I works fine to some extent.
    Table contain 240 rows & array size 100 but now it prints 92 records only (8 records not printed) and the value of ‘J’ at the end of select statement is not printed.
    why it is so..

    begin-program
    let #j=0
    let #i=0
    create-array name=emparr size=100
    field=eno:number
    field=empname:char
    field=sall:number

    begin-select
    empno
    ename
    sal
    put &empno &ename &sal into emparr(#j)
    do checkarray
    let #j=#j+1
    from test_emp
    end-select

    Print #j (+1) center bold
    end-program

    begin-procedure checkarray
    while #i <= #j
    get #eno $empname #sall from emparr(#i)
    print #eno (+1,5)
    print $empname (,15)
    print #sall (,25)
    let #i=#i+1
    end-while
    print ‘ ‘ (+1)
    end-procedure

    Thanks,
    AnandhRaj

    • administrator says:

      Please e-mail your print file and your log file to me at steven@peoplesoftsqr.com. Also, while this is an interesting problem, I’m wondering why you’re trying to read 240 rows into a 100 row array. Our goal now is to debug a program that is designed to fail, to make it fail in the “right” way.

  5. Anand says:

    Of course, what you are saying is right.
    I will mail you the output.

    Thanks
    AnandhRaj