Database Reference
In-Depth Information
PREVIOUS(cell)
. Returns a value at the beginning of each iteration
or loop.
ITERATION_NUMBER
. Returns a completed loop iteration
sequence number; the subscript of a loop.
11.5.3
Using the SPREADSHEET (MODEL) Clause
So how do we demonstrate use of the SPREADSHEET clause in a simplis-
tic manner? Good question. Looks nasty, doesn't it? Well, it is! However,
without a tool such as the SPREADSHEET clause, this would be much
more complicated. Let's once again use our data warehouse SALES fact
table. Here's a simple query, creating a view (best in a data warehouse as a
materialized view) on the SALES table, with various dimensions thrown in.
The query summarizes sales, breaking them down into continent, country,
and year, including sales quantities and revenues.
CREATE VIEW SALESSUM AS
SELECT CT.NAME AS CONTINENT, CY.NAME AS COUNTRY
, TO_NUMBER(TO_CHAR(S.SALE_DATE, 'YYYY')) AS YEAR
, COUNT(S.SALE_QTY) AS SALES
, SUM(S.SALE_PRICE) AS REVENUE
FROM CONTINENT CT, COUNTRY CY, SALES S
WHERE CT.CONTINENT_ID = S.CONTINENT_ID
AND CY.COUNTRY_ID = S.COUNTRY_ID
GROUP BY CT.NAME, CY.NAME, TO_CHAR(S.SALE_DATE, 'YYYY');
Now let's take a quick peek at the data in the view, limiting to North
America only, as shown in Figure 11.21.
COLUMN CONTINENT FORMAT A16
COLUMN COUNTRY FORMAT A16
COLUMN YEAR FORMAT 9999
COLUMN SALES FORMAT 990
COLUMN REVENUE FORMAT $999,999.00
SELECT * FROM SALESSUM WHERE CONTINENT IN ('North America');
Now let's use the 2003 and 2004 figures to project estimates into 2005,
as shown in Figure 11.22. The following script is the query. Note the calcu-
lation of the 2005 quarterly projection as being the following (((2004 -
