/*
|| 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