Database Reference
In-Depth Information
Figure 11.24
For Loops in the
SPREADSHEET
Clause.
COLUMN QS FORMAT 9990 HEADING "Sales"
COLUMN QR FORMAT $9,990.99 HEADING "Revenue"
SELECT CONTINENT, COUNTRY, YEAR, QS, QR FROM SALESSUM S
WHERE CONTINENT IN ('Europe', 'North America')
SPREADSHEET
PARTITION BY (CONTINENT, COUNTRY)
DIMENSION BY (YEAR)
MEASURES (S.SALES QS, S.REVENUE QR)
RULES
(
QS[2005]=(((QS[2004]-QS[2003])/QS[2004])+1)*QS[2004]
,QR[2005]=(((QR[2004]-QR[2003])/QR[2004])+1)*QR[2004]
)
ORDER BY CONTINENT DESC, COUNTRY, YEAR;
Using the SPREADSHEET clause, one can even use
for
loops to scroll
through sets of rows and create multidimensional cross-tabulations of all
cells in a query. The example in Figure 11.24 shows 2003 and 2004 figures
summed into 2005, for all countries. Unlike Figures 11.22 and 11.23, the
example in Figure 11.24 is a sum rather than a projection based on the
