Overview
Rotated Arrays (RMS and VSAM only) enable you to creation a logical view of repeating data so that each column in the repeating segment appears as a unique row. A benefit to using rotated arrays is that aggregate functions, such as SUM, can be applied. The following is an example of a rotated array. The record shown is an order summary.
CustomerID | Jan Sales | Feb Sales | Mar Sales | April Sales | May Sales | June Sales |
CUST876762 | $400 | $50 | $20 | $90 | $98 | $85 |
By treating the sales_month array as a rotated array, results are returned as follows:
CustomerID | Month | Sales | CNXARRAYCOLUMN |
CUST876762 | Jan | $400 | 0 |
CUST876762 | Feb | $50 | 1 |
CUST876762 | Mar | $20 | 2 |
CUST876762 | April | $90 | 3 |
CUST876762 | May | $98 | 4 |
CUST876762 | June | $85 | 5 |
Returning data in this form provides two benefits. First, many tools cannot use ODBC tables containing more than 255 columns. If a table contains a 400-element array, it cannot be imported into some of the tools. However, by rotating the array, that tool is able to read the array as a single column, thereby allowing manipulation of data. This has the effect of normalizing nonrelational data.
The pseudo-column CNXARRAYCOLUMN displayed in the above example is returned whenever the rotated array feature is used. This column represents the element number of the array for this row of data.
Secondly, mathematical functions such as SUM, AVG, MIN, and MAX are easily applied to numeric data in column format. In the following example, trying to return the sales average would be much more difficult without the rotated array.
Without rotated array:
SELECT (JanSales + FebSales + MarSales + AprSales + MaySales + JuneSales + JulySales + AugSales + SeptSales + OctSales + NovSales + DecSales)/12 as SalesAverage from ORDER_SUMMARY
With rotated array:
SELECT AVG(MonthSales) as SalesAverage from ORDER_SUMMARY