/*
|| Oracle 10g SQL Enhancements - Listing 3
||
|| Contains examples of new Oracle 10g SQL features available via the new
|| MODEL clause.
||
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10g's
|| new SQL features and should be carefully proofread before executing it
|| against any existing Oracle database to insure that no potential damage
|| can occur.
||
*/
-----
-- Listing 3.1: Recreate the a view that shows Sales for selected Products
-- only for selected U.S. Midwestern States
-----
CREATE OR REPLACE VIEW sh.sales_midwest_only AS
SELECT
CT.cust_state_province state
,P.prod_id prod
,P.prod_name NAME
,P.prod_category ctgy
,T.calendar_year year
,SUM(S.amount_sold) sold
,COUNT(S.amount_sold) cnt
FROM
sh.sales S
,sh.times T
,sh.customers CT
,sh.products P
WHERE S.time_id = T.time_id
AND S.prod_id = P.prod_id
AND S.cust_id = CT.cust_id
AND CT.country_id = 52790
AND CT.cust_state_province IN ('IL','IN','MI','MN','WI')
AND P.prod_id BETWEEN 100 AND 200
GROUP BY
CT.cust_state_province
,P.prod_id
,P.prod_category
,P.prod_name
,T.calendar_year;
-----
-- Listing 3.2: A simple example of performing inter-row calculations
-- with the new MODEL clause
-----
-- Within each geographic state and product, summarize sales for selected
-- products and group the resulting sales based on the following business
-- rules:
-- (a) 125 and 126 together as a new product, 99910
-- (b) 127 though 130 together as a new product, 99920
-- (c) the difference between all sales for product 99920 and 99910 into
-- another new product, 99999
-----
SELECT state, prod, total_sales
FROM sh.sales_midwest_only
WHERE prod BETWEEN 125 AND 130
GROUP BY state, prod
MODEL
PARTITION BY (state)
DIMENSION BY (prod)
MEASURES (SUM(sold) AS total_sales)
IGNORE NAV
UNIQUE DIMENSION
RULES UPSERT
(
total_sales[99910] =
total_sales[prod=125] + total_sales[prod=126],
total_sales[99920] =
total_sales[prod=127] + total_sales[prod=128]
+ total_sales[prod=129] + total_sales[prod=130],
total_sales[99999] =
total_sales[99920] - total_sales[99910]
)
ORDER BY state, prod;
>>> Results:
Using MODEL With Symbolic Cell References
State Prod # Total Sales
----- ------- ---------------
IL 125 5,578.77
IL 126 8,727.75
IL 127 21,469.20
IL 128 12,397.34
IL 129 30,232.30
IL 130 42,297.30
IL 99910 14,306.52
IL 99920 106,396.14
IL 99999 92,089.62
IN 125 1,502.80
IN 126 2,568.25
IN 127 4,643.41
IN 128 3,818.99
IN 129 11,723.54
IN 130 13,722.44
IN 99910 4,071.05
IN 99920 33,908.38
IN 99999 29,837.33
MI 125 5,378.74
MI 126 7,159.84
MI 127 13,625.42
MI 128 11,473.04
MI 129 31,855.91
MI 130 38,080.44
MI 99910 12,538.58
MI 99920 95,034.81
MI 99999 82,496.23
MN 125 4,424.99
MN 126 6,145.24
MN 127 13,666.63
MN 128 10,880.50
MN 129 38,367.86
MN 130 32,667.93
MN 99910 10,570.23
MN 99920 95,582.92
MN 99999 85,012.69
WI 125 2,212.95
WI 126 3,048.68
WI 127 5,874.44
WI 128 5,384.26
WI 129 4,957.83
WI 130 19,851.03
WI 99910 5,261.63
WI 99920 36,067.56
WI 99999 30,805.93
45 rows selected.
-----
-- Listing 3.3: Using positional cell references with MODEL
-- Note that because the initial query limits the
-- data retrieved only to years of 2000 or greater,
-- the calculated row for year 1999 returns a NULL
-- because it uses a positional, not symbolic,
-- reference.
-----
COL state FORMAT A5 HEADING 'State'
COL prod FORMAT 999999 HEADING 'Prod #'
COL year FORMAT 9999 HEADING 'Year'
COL total_sales FORMAT 999,999,999.99 HEADING 'Total Sales'
TTITLE 'Using MODEL With Positional Cell References'
SELECT
state
,prod
,year
,total_sales
FROM sh.sales_midwest_only
WHERE prod BETWEEN 125 AND 127
AND state IN ('IL', 'WI')
AND year >= 2000
MODEL
PARTITION BY (state, prod)
DIMENSION BY (year)
MEASURES (sold total_sales)
RULES UPSERT
SEQUENTIAL ORDER
(
total_sales[1999] =
total_sales[1998]
+ total_sales[1999],
total_sales[2099] =
total_sales[2000]
+ total_sales[2001]
)
ORDER BY state, prod, year;
>>> Results:
Using MODEL With Positional Cell References
State Prod # Year Total Sales
----- ------- ----- ---------------
IL 125 1999
IL 125 2000 449.92
IL 125 2001 630.48
IL 125 2099 1,080.40
IL 126 1999
IL 126 2000 726.44
IL 126 2001 939.03
IL 126 2099 1,665.47
IL 127 1999
IL 127 2000 2,700.51
IL 127 2001 1,569.56
IL 127 2099 4,270.07
WI 125 1999
WI 125 2000 146.63
WI 125 2001 48.62
WI 125 2099 195.25
WI 126 1999
WI 126 2000 239.10
WI 126 2001 29.95
WI 126 2099 269.05
WI 127 1999
WI 127 2000 978.73
WI 127 2001 535.46
WI 127 2099 1,514.19
24 rows selected.
-----
-- Listing 3.4: Create and populate a new table (SH.BALANCE_SHEETS)
-- to demonstrate the ITERATE directive of the MODEL clause
-----
DROP TABLE sh.balance_sheets;
CREATE TABLE sh.balance_sheets (
acct# VARCHAR2(4)
,name VARCHAR2(30)
,balance NUMBER(15,2)
);
INSERT INTO sh.balance_sheets
VALUES('1010', 'Cash' , 100000.00);
INSERT INTO sh.balance_sheets
VALUES('2010', 'Accounts Receivable' , 375000.00);
INSERT INTO sh.balance_sheets
VALUES('2020', 'Cash Discount Due' , 0.00);
INSERT INTO sh.balance_sheets
VALUES('2030', 'Earned Interest' , 0.00);
INSERT INTO sh.balance_sheets
VALUES('3020', 'Taxable Dividends' , 0.00);
COMMIT;
-----
-- Listing 3.5: Using the ITERATE directive of the MODEL clause to cycle
-- through business rules several times
-----
SQL> COL acct# FORMAT A20 HEADING 'Account#'
SQL> COL fb FORMAT 999,999,999.99 HEADING 'Projected|Balance'
SQL>
SQL> TTITLE 'Sample Cash Balance Projection - 1 Cycle'
SQL> SELECT
2 acct#, fb
3 FROM sh.balance_sheets
4 MODEL
5 DIMENSION BY (acct#)
6 MEASURES (balance fb)
7 RULES ITERATE (3)
8 (
9 fb['3020'] =
((fb['1010'] + fb['2010'] - fb[2020] + fb[2030]) * 0.15)
10 ,fb['2030'] = ((fb['1010'] + fb['2010'] - fb[2020]) * 0.075)
11 ,fb['2020'] = (fb['1010'] + fb['2010'] * 0.30)
12 );
Sample Cash Balance Projection - 1 Cycle
Projected
Account# Balance
-------------------- ---------------
1010 100,000.00
2010 375,000.00
2020 212,500.00
2030 19,687.50
3020 42,328.13
SQL>
SQL> TTITLE 'Sample Cash Balance Projection - 2 Cycles'
SQL> SELECT
2 acct#, fb
3 FROM sh.balance_sheets
4 MODEL
5 DIMENSION BY (acct#)
6 MEASURES (balance fb)
7 RULES ITERATE (2)
8 (
9 fb['3020'] =
((fb['1010'] + fb['2010'] - fb[2020] + fb[2030]) * 0.15)
10 ,fb['2030'] = ((fb['1010'] + fb['2010'] - fb[2020]) * 0.075)
11 ,fb['2020'] = (fb['1010'] + fb['2010'] * 0.30)
12 );
Sample Cash Balance Projection - 2 Cycles
Projected
Account# Balance
-------------------- ---------------
1010 100,000.00
2010 375,000.00
2020 212,500.00
2030 19,687.50
3020 44,718.75
SQL>
SQL> TTITLE 'Sample Cash Balance Projection - 3 Cycles'
SQL> SELECT
2 acct#, fb
3 FROM sh.balance_sheets
4 MODEL
5 DIMENSION BY (acct#)
6 MEASURES (balance fb)
7 RULES ITERATE (3)
8 (
9 fb['3020'] =
((fb['1010'] + fb['2010'] - fb[2020] + fb[2030]) * 0.15)
10 ,fb['2030'] = ((fb['1010'] + fb['2010'] - fb[2020]) * 0.075)
11 ,fb['2020'] = (fb['1010'] + fb['2010'] * 0.30)
12 );
Sample Cash Balance Projection - 3 Cycles
Projected
Account# Balance
-------------------- ---------------
1010 100,000.00
2010 375,000.00
2020 212,500.00
2030 19,687.50
3020 42,328.13