Oracle Model Clause
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
Purpose  
Syntax MODEL
[[<KEEP | IGNORE>] NAV] [UNIQUE [<DIMENSION | SINGLE REFERENCE>]
[RETURN <UPDATED | ALL> ROWS]
[reference_model]
[main_model]
[partition by (<cols>)]
dimension by (<cols>)
measures (<cols>)
[ignore nav ] | [ keep nav]
[rules
  [ upsert | update]
  [automatic order | sequential order]
  [iterate (n) [ until <condition>]]
(<cell_assignment> = <expression> ...)
Demo Preparation conn sh/sh@pdbdev

CREATE MATERIALIZED VIEW sales_view AS
SELECT co.country_name country, p.prod_name prod, (t.calendar_year+9) sale_year,
SUM(s.amount_sold) sale, COUNT(s.amount_sold) cnt
FROM sales s, times t, customers cu, countries co, products p
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND s.cust_id = cu.cust_id
AND cu.country_id = co.country_id
GROUP BY country_name, prod_name, calendar_year;
 
Basic
Basic Model Clause This statement partitions data by country, so the formulas are applied to data of one country at a time. Our sales fact data ends with 2011, so any rules defining values for 2002 or later will insert new cells. The first rule defines the sales of a video games called "Bounce" in 2002 as the sum of its sales in 2010 and 2011. The second rule defines the sales for Y Box in 2012 to be the same value they were for 2011. The third rule defines a product called "2_Products," which is simply the sum of the Bounce and Y Box values for 2002. Since the values for 2_Products are derived from the results of the two prior formulas, the rules for Bounce and Y Box must be executed before the 2_Products rule.
col country format a20
col prod format a20

SELECT country, prod, sale_year, sales
FROM sales_view
WHERE country IN ('Italy','Japan')
MODEL RETURN UPDATED ROWS
  PARTITION BY (country)
  DIMENSION BY (prod, sale_year)
  MEASURES (sale sales)
  RULES (
    sales['Bounce', 2012] = sales['Bounce', 2010] + sales['Bounce', 2009],
    sales['Y Box', 2012] = sales['Y Box', 2010],
    sales['2_Products', 2012] = sales['Bounce', 2010] + sales['Y Box',2009])
ORDER BY country, prod, sale_year;

COUNTRY             PROD          SALE_YEAR    SALES
------------------- ------------ ---------- --------
Italy               2_Products         2012 34169.19
Italy               Bounce             2012  9179.99
Italy               Y Box              2012 81207.55
Japan               2_Products         2012 51994.26
Japan               Bounce             2012 11437.13
Japan               Y Box              2012 89634.83
Positional Cell Reference – Single cell access and Upserts The value for the cell reference is matched to the appropriate dimension based on its position in the expression. The DIMENSION BY clause of the model determines the position assigned to each dimension: in this case, the first position is product ("prod") and the second position is year.
SELECT country, prod, sale_year, sales
FROM sales_view
WHERE country = 'Italy'
MODEL RETURN UPDATED ROWS
  PARTITION BY (country)
  DIMENSION BY (prod, sale_year)
  MEASURES (sale sales)
  RULES (
    sales['Bounce', 2010] = 10)
ORDER BY country, prod, sale_year;

COUNTRY             PROD          SALE_YEAR    SALES
------------------- ------------ ---------- --------
Italy               Bounce             2010       10
Adding a new cell The formula in the query below sets the year value to 2015 and thus creates a new cell in the array.
SELECT country, prod, sale_year, sales
FROM sales_view
WHERE country = 'Italy'
MODEL RETURN UPDATED ROWS
  PARTITION BY (country)
  DIMENSION BY (prod, sale_year)
  MEASURES (sale sales)
  RULES (
    sales['Bounce', 2015] = 20)
ORDER BY country, prod, sale_year;

COUNTRY             PROD          SALE_YEAR    SALES
------------------- ------------ ---------- --------
Italy               Bounce             2015       20
Symbolic Cell Reference: Multi-Cell Access And Updates The query, below, uses "symbolic cell reference." With symbolic cell references, the standard SQL conditions are used to determine the cells which are part of a formula. Use conditions such as <,>, IN, and BETWEEN. In this example the formula applies to any cell which has product value equal to Bounce and a year value greater than 2009. The example shows how a single formula can access multiple cells.
SELECT country,  prod, sale_year, sales
FROM sales_view
WHERE country = 'Italy'
MODEL RETURN UPDATED ROWS
  PARTITION BY (country)
  DIMENSION BY (prod, sale_year)
  MEASURES (sale sales)
  RULES (
    sales[prod='Bounce', sale_year>2009] = 10)
ORDER BY country, prod, sale_year;

COUNTRY             PROD          SALE_YEAR    SALES
------------------- ------------ ---------- --------
Italy                Bounce          2010         10
Positional And Symbolic Cell References In A Single Query Since our example data has no values beyond the year 2011, any rule involving the year 2012 or later requires insertion of a new cell. The same applies to any new product name we define here. In the third formula we define a new product '2_Products' for 2005, so a cell will be inserted for it. The first rule, for Bounce in 2012, inserts new cells since it is positional notation. The second rule, for Y Box, uses symbolic notation, but since there are already values for 'Y Box' in the year 2011, it updates those values.The third rule, for '2_Products' in 2015, is positional, so it can insert new cells, and we see them in the output.
SELECT country, prod, sale_year, sales
FROM sales_view
WHERE country IN ('Italy','Japan')
MODEL RETURN UPDATED ROWS
  PARTITION BY (country)
  DIMENSION BY (prod, sale_year)
  MEASURES (sale sales)
  RULES (
    sales['Bounce', 2012] = sales['Bounce', sale_year = 2010],
    --positional notation: can insert new cell
    sales['Y Box', sale_year>2000] = sales['Y Box', 2009],
    --symbolic notation: can update existing cell
    sales['2_Products', 2011] = sales['Bounce', 2010] + sales['Y Box', 2009])
    --positional notation: permits creation of new cell  for new product
ORDER BY country, prod, sale_year;

COUNTRY             PROD          SALE_YEAR    SALES
------------------- ------------ ---------- --------
Italy               2_Products         2011 34169.19
Italy               Bounce             2012  4846.30
Italy               Y Box              2008 29322.89
Italy               Y Box              2009 29322.89
Italy               Y Box              2010 29322.89
Japan               2_Products         2011 51994.26
Japan               Bounce             2012  6303.60
Japan               Y Box              2008 45690.66
Japan               Y Box              2009 45690.66
Japan               Y Box              2010 45690.66
Multi-Cell References On The Right Side Of A Formula In the query above we use a BETWEEN condition to specify multiple cells on the right side of the formula, and these are aggregated to a single value with the MAX() function.
SELECT country, prod, sale_year, sales
FROM sales_view
WHERE country = 'Italy'
MODEL RETURN UPDATED ROWS
  PARTITION BY (country)
  DIMENSION BY (prod, sale_year)
  MEASURES (sale sales)
  RULES (
    sales['Bounce', 2011] = 100 + MAX(sales)['Bounce', sale_year BETWEEN 2008 AND 2010])
ORDER BY country, prod, sale_year;

COUNTRY             PROD          SALE_YEAR    SALES
------------------- ------------ ---------- --------
Italy               Bounce             2011  4946.30
 
CV Function
Use Left-Side Values In Right Side Calculations The two CV() functions used in the formula return the year dimension value of the cell currently referenced on the left side. When the left side of the formula above references the cell 'Bounce' and 1999, the right side expression would resolve to:
sales['Mouse Pad', 2009] + 0.2 * sales['Y Box', 2009]

Similarly, when the left side references the cell 'Bounce' and 2000, the right side expression we would evaluate is:
sales['Mouse Pad', 2010] + 0.2 * sales['Y Box', 2010]

CV() function takes a dimension key as its argument. It is also possible to use CV() without any argument as in CV() which causes positional referencing. Therefore the formula above can be written as:
s['Bounce', year BETWEEN 2005 AND 2012] = s['Mouse Pad', CV()] + 0.2 * s['Y Box', CV()]
SELECT country, prod, sale_year, ROUND(sales,2)
FROM sales_view
WHERE country ='Italy'
MODEL RETURN UPDATED ROWS
  PARTITION BY (country)
  DIMENSION BY (prod, sale_year)
  MEASURES (sale sales)
  RULES (
    sales['Bounce', sale_year BETWEEN 2008 AND 2010] = sales['Mouse Pad',
    CV(sale_year)] + 0.2 * sales['Y Box', CV(sale_year)])
ORDER BY country, prod, sale_year;

COUNTRY             PROD          SALE_YEAR    SALES
------------------- ------------ ---------- --------
Italy               Bounce             2008  7706.27
Italy               Bounce             2009  9527.41
Italy               Bounce             2010 20989.41
Used In Expressions For Inter-Row Calculations Note that the blank cells in the results are NULLs. The formula results in a null if there is no value for the product two years earlier. None of the products has a value for 2008, so in each case the 2009 growth calculation is NULL.
SELECT country, prod, sale_year, sales, round(growth_pct,2) growth_pct
FROM sales_view
WHERE country = 'Italy'
MODEL RETURN UPDATED ROWS
  PARTITION BY (country)
  DIMENSION BY (prod, sale_year)
  MEASURES (sale sales, 0 growth_pct)
  RULES (
    growth_pct[prod IN ('Bounce','Y Box', 'Mouse Pad'),
    sale_year BETWEEN 2008 and 2011]
= 100 * (sales[CV(prod), CV(sale_year)] -
    sales[CV(prod), CV(sale_year)-1]
) / sales[CV(prod), CV(sale_year)-1])
ORDER BY country, prod, sale_year;

COUNTRY             PROD          SALE_YEAR    SALES  GROWTH_PCT
------------------- ------------ ---------- -------- -----------
Italy               Bounce             2008  2474.78
Italy               Bounce             2009  4333.69       75.11
Italy               Bounce             2010  4846.30       11.83
Italy               Mouse Pad          2008  4663.24       52.61
Italy               Mouse Pad          2009  3662.83      -21.45
Italy               Mouse Pad          2010  4747.90       29.62
Italy               Y Box              2008 15215.16
Italy               Y Box              2009 29322.89       92.72
Italy               Y Box              2010 81207.55      176.94
 
Wildcard With ANY Keyword
ANY Keyword Demo Note that in the MEASURES clause above, we use the placeholder value of 0 when specifying the new measure growth_pct. Other numbers would also work as placeholder values. This query gives the same results as the prior query because the full data set ranges from 2008 to 2011, and that is the range specified in the prior query.

ANY can be used in cell references to include all dimension values including NULLs. In symbolic reference notation, we use the phrase "IS ANY". Note that the ANY wildcard prevents cell insertion when used with either positional or symbolic notation.
SELECT country, prod, sale_year, sales, round(growth_pct, 2) growth_pct
FROM sales_view
WHERE country = 'Italy'
MODEL RETURN UPDATED ROWS
  PARTITION BY (country)
  DIMENSION BY (prod, sale_year)
  MEASURES (sale sales, 0 growth_pct)
  RULES (
    growth_pct[prod IN ('Bounce','Y Box','Mouse Pad'), ANY] =
    100 * (sales[CV(prod), CV(sale_year)] - sales[CV(prod), CV(sale_year)-1]
)
    / sales[CV(prod), CV(sale_year) -1])
ORDER BY country, prod, sale_year;

COUNTRY             PROD          SALE_YEAR    SALES  GROWTH_PCT
------------------- ------------ ---------- -------- -----------
Italy               Bounce             2008  2474.78
Italy               Bounce             2009  4333.69       75.11
Italy               Bounce             2010  4846.30       11.83
Italy               Mouse Pad          2007  3055.69
Italy               Mouse Pad          2008  4663.24       52.61
Italy               Mouse Pad          2009  3662.83      -21.45
Italy               Mouse Pad          2010  4747.90       29.62
Italy               Y Box              2008 15215.16
Italy               Y Box             2009  29322.89       92.72
Italy               Y Box              2010 81207.55      176.94
 
FOR Loops
FOR LOOP To Create Specify New Cells By using positional notation on the left side of the formulas, we ensure that cells for these products in the year 2015 will be inserted if they are not already present in the array. This technique is bulky since it requires as many formulas as there are products. If we have to work with dozens of products, it becomes an unwieldy approach. With FOR we can reword this computation so it is concise yet has exactly the same behavior.

The FOR construct can be thought of as a tool to make a single formula generate multiple formulas with positional references, thus enabling creation of new cells (UPSERT behavior).
SELECT country,  prod, sale_year, round(sales, 2) sales
FROM sales_view
WHERE country = 'Italy'
MODEL RETURN UPDATED ROWS
  PARTITION BY (country)
  DIMENSION BY (prod, sale_year)
  MEASURES (sale sales)
  RULES (
    sales[FOR prod IN ('Mouse Pad', 'Bounce', 'Y Box'), 2005] = 1.3 * sales[CV(prod), 2010])
ORDER BY country, prod, sale_year;

COUNTRY             PROD          SALE_YEAR    SALES
------------------- ------------ ---------- --------
Italy               Bounce             2005   6300.19
Italy               Mouse Pad          2005   6172.27
Italy               Y Box              2005 105569.82
FOR LOOP To Range Over A Value Sequence Project sales values of Mouse Pad for the years 2012 to 2015 so that they are equal to 120% of the value in 2001 using the sequence generation of the FOR construct.

This kind of FOR construct can be used for dimensions of numeric, date and datetime datatypes. The increment/decrement expression of the FOR loop should be numeric for numeric dimensions and can be numeric or interval for dimensions of date or datetime types.
SELECT country, prod, sale_year, round(sales, 2) sales
FROM sales_view
WHERE country = 'Italy'
MODEL RETURN UPDATED ROWS
  PARTITION BY (country)
  DIMENSION BY (prod, sale_year)
  MEASURES (sale sales)
  RULES (
    sales['Mouse Pad', FOR sale_year FROM 2008 TO 2010 INCREMENT 1] = sales['Mouse Pad',
    sale_year=CV(sale_year)-1] * 1.2)
ORDER BY country, prod, sale_year;

COUNTRY             PROD          SALE_YEAR    SALES
------------------- ------------ ---------- --------
Italy               Mouse Pad          2008  3666.83
Italy               Mouse Pad          2009  4400.19
Italy               Mouse Pad          2010  5280.23
 
Order Of Evaluation
Automatic Ordering Of Rule Application By default, formulas are evaluated in the order they appear in the MODEL clause.

The following query uses Automatic Order, so it recognizes that Bounce and Y Box sales must be calculated before 2_Products sales.
SELECT country, prod, sale_year, sales
FROM sales_view
WHERE country IN ('Italy', 'Japan')
MODEL RETURN UPDATED ROWS
PARTITION BY (country)
DIMENSION BY (prod, sale_year)
MEASURES (sale sales)
RULES AUTOMATIC ORDER (
  sales['2_Products', 2012] = sales['Bounce', 2009] + sales['Y Box', 2010],
  sales['Bounce', 2012] = sales['Bounce', 2009] + sales['Bounce', 2010],
  sales['Y Box', 2009] = sales['Y Box', 2010])
ORDER BY country, prod, sale_year;

COUNTRY             PROD          SALE_YEAR    SALES
------------------- ------------ ---------- --------
Italy               2_Products 2012 85541.24
Italy               Bounce 2012 9179.99
Italy               Y Box 2009 81207.55
Japan               2_Products 2012 94768.36
Japan               Bounce 2012 11437.13
Japan               Y Box 2009 89634.83
Sequential Ordering Of Rule Application Same as the above but with sequential ordering. This query uses Sequential Order, so it does not calculate the values for Bounce and Y Box before 2_Products, and 2_Products is assigned as NULL.
SELECT country, prod, sale_year, sales
FROM sales_view
WHERE country IN ('Italy', 'Japan')
MODEL RETURN UPDATED ROWS
  PARTITION BY (country)
  DIMENSION BY (prod, sale_year)
  MEASURES (sale sales)
  RULES SEQUENTIAL ORDER (
    sales['2_Products', 2012] = sales['Bounce', 2009] + sales['Y Box', 2010],
    sales['Bounce', 2012] = sales['Bounce', 2009] + sales['Bounce', 2010],
    sales['Y Box', 2009] = sales['Y Box', 2010])
ORDER BY country, prod, sale_year;

COUNTRY             PROD          SALE_YEAR    SALES
------------------- ------------ ---------- --------
Italy               2_Products 2012 85541.24
Italy               Bounce 2012 9179.99
Italy               Y Box 2009 81207.55
Japan               2_Products 2012 94768.36
Japan               Bounce 2012 11437.13
Japan               Y Box 2009 89634.83
 
Null Measures And Missing Cells
NULL respected By default, NULL cell measure values are treated the same way as NULLs are treated elsewhere in SQL. Missing cells are treated as cells with NULL measure value.
SELECT country, prod, sale_year, sales
FROM sales_view
WHERE country = 'Italy'
MODEL RETURN UPDATED ROWS
  PARTITION BY (country)
  DIMENSION BY (prod, sale_year)
  MEASURES (sale sales)
  RULES (
    sales['Mouse Pad', 2011] = sales['Mouse Pad', 2009] + sales['Mouse Pad', 2010])
ORDER BY country, prod, sale_year;

COUNTRY             PROD          SALE_YEAR    SALES
------------------- ------------ ---------- --------
Italy               Mouse Pad           2011 8410.73
NULL ignored Once a NULL has been found in a set of values, it can cause many dependent calculations to result in NULLs. Therefore it can be more useful to treat NULLS and missing values as non-NULL values. In this way, NULLs will not be propagated through a set of calculations. You can use the IGNORE NAV option (NAV stands for non-available values) to default NULLs and missing cells to the following values:
  • 0 for numeric data
  • empty string for character/string data
  • 01-JAN-2001 for data type data
  • NULL for other data types

Note that the default behavior is KEEP NAV which treats NULLs in the standard manner and treats missing values as NULLs. When we add the IGNORE NAV option to the query above, it returns a numeric value for sales even though the value for 2004 is missing.
SELECT country, prod, sale_year, sales
FROM sales_view
WHERE country = 'Singapore'
MODEL IGNORE NAV RETURN UPDATED ROWS
PARTITION BY (country)
DIMENSION BY (prod, sale_year)
MEASURES (sale sales)
RULES (
sales['Mouse Pad', 2011] = sales['Mouse Pad', 2009] + sales['Mouse Pad', 2010] + (sales['Mouse Pad', 2011])/2)
ORDER BY country, prod, sale_year;

COUNTRY             PROD          SALE_YEAR    SALES
------------------- ------------ ---------- --------
Singapore           Mouse Pad          2011  3473.98
 
Reference Models
Calculate projected sales figures for two different countries and show these projections in both the country currency and US dollars REFERENCE model_name ON (query)
DIMENSION BY (cols)
MEASURES (cols) [reference options]
CREATE TABLE dollar_conv(
country       VARCHAR2(30),
exchange_rate NUMBER);

INSERT INTO dollar_conv VALUES('Canada', 0.75);
INSERT INTO dollar_conv VALUES('Brazil', 0.34);
COMMIT;

col localsales format 9,999,999.99
col dollarsales format 999,999.99

SELECT country, sale_year, localsales, dollarsales
FROM sales_view
WHERE country IN ('Canada', 'Brazil')
GROUP BY country, sale_year
MODEL RETURN UPDATED ROWS
  REFERENCE conv_refmodel ON (
    SELECT country, exchange_rate AS er FROM dollar_conv)
  DIMENSION BY (country) MEASURES (er) IGNORE NAV

  MAIN main_model
  DIMENSION BY (country, sale_year)
  MEASURES (SUM(sale) sales, 0 localsales, 0 dollarsales)
  IGNORE NAV RULES (
    -- assuming that sales in Canada grow by 22% */
    localsales['Canada', 2011] = sales[CV(country), 2010] * 1.22,
    dollarsales['Canada', 2011] = sales[CV(country), 2010] * 1.22 * conv_refmodel.er['Canada'],
    -- assuming that sales in Brazil grow by 28% */
    localsales['Brazil', 2011] = sales[CV(country), 2009] * 1.28,
    dollarsales['Brazil', 2011] = sales[CV(country), 2009] * 1.28 * er['Brazil']);

COUNTRY   SALE_YEAR   LOCALSALES  DOLLARSALES
-------- ---------- ------------ ------------
Brazil         2011     17,125.17    5,822.56
Canada         2011  1,048,246.22  786,184.66
Working With Arrays Of Different Dimensionality Calculate sales for Brazil and Canada, applying the 2005 growth figures and converting the values to US dollars.
CREATE TABLE growth_rate(
country     VARCHAR2(30),
year        NUMBER,
growth_rate NUMBER);

INSERT INTO growth_rate VALUES('Brazil', 2012, 17);
INSERT INTO growth_rate VALUES('Brazil', 2011, 28);
INSERT INTO growth_rate VALUES('Canada', 2012, 13);
INSERT INTO growth_rate VALUES('Canada', 2011, 22);
COMMIT;

col dollarsales format 999,999.99

SELECT country, sale_year, localsales, dollarsales
FROM sales_view
WHERE country IN ('Canada','Brazil')
GROUP BY country, sale_year
MODEL RETURN UPDATED ROWS
  REFERENCE conv_refmodel ON (
    SELECT country, exchange_rate FROM dollar_conv)
  DIMENSION BY (country c)
  MEASURES (exchange_rate er) IGNORE NAV
  REFERENCE growth_refmodel ON (
    SELECT country, year, growth_rate FROM growth_rate
)
  DIMENSION BY (country c, year y)
  MEASURES (growth_rate gr) IGNORE NAV
  MAIN main_model
  DIMENSION BY (country, sale_year)
  MEASURES (SUM(sale) sales, 0 localsales, 0 dollarsales) IGNORE NAV
  RULES (
    localsales[FOR country IN ('Brazil', 'Canada'), 2011] = sales[CV(country), 2009] * (100+gr[CV(country), CV(sale_year)])/100 ,
    dollarsales[FOR country IN ('Brazil', 'Canada'),2011] = sales[CV(country), 2010] * (100 + gr[CV(country), CV(sale_year)])/100 * er[CV(country)])
ORDER BY country;

COUNTRY   SALE_YEAR   LOCALSALES  DOLLARSALES
-------- ---------- ------------ ------------
Brazil         2011    17,125.17     2,262.12
Canada          2011  794,204.25   786,184.66
 
ITERATE Models
Iterate through and evaluate formulas a specified number of times ITERATE (<number_of_iterations>)
CREATE TABLE ledger (
account VARCHAR2(20),
balance NUMBER(10,2));

INSERT INTO ledger VALUES ('Salary', 100000);
INSERT INTO ledger VALUES ('Capital_gains', 15000);
INSERT INTO ledger VALUES ('Net', 0);
INSERT INTO ledger VALUES ('Tax', 0);
INSERT INTO ledger VALUES ('Interest', 0);
COMMIT;

col bal format 999,999.99

SELECT account, round(bal, 2) bal
FROM ledger
MODEL IGNORE NAV
  DIMENSION BY (account)
  MEASURES (balance bal)
  RULES SEQUENTIAL ORDER
  ITERATE (100) (bal['Net'] = bal['Salary'] + bal['Capital_gains'] - bal['Interest'] - bal['Tax'],
  bal['Tax'] = (bal['Salary'] - bal['Interest']) * 0.38 + bal['Capital_gains'] * 0.28,
  bal['Interest'] = bal['Net'] * 0.30);

ACCOUNT               BAL
-------------- ----------
Salary          100000.00
Capital_gains    15000.00
Net              61382.80
Tax              35202.36
Interest         18414.84
 
ITERATE With UNTIL Condition
Until Condition Demo ITERATE (<number_of_iterations>) UNTIL (some_condition_is_met>)
col bal format 999,999.99

SELECT account, bal
FROM ledger
MODEL IGNORE NAV
  DIMENSION BY (account)
  MEASURES (balance bal)
  RULES SEQUENTIAL ORDER
  ITERATE (100) UNTIL (ABS(
  (PREVIOUS(bal['Net'])-bal['Net'])) < 0.01)
  (bal['Net'] = bal['Salary'] + bal['Capital_gains'] - bal['Interest'] - bal['Tax'],
   bal['Tax'] = (bal['Salary'] - bal['Interest']) * 0.38 + bal['Capital_gains'] *0.28,
   bal['Interest'] = bal['Net'] * 0.30, bal['Iteration Count'] = ITERATION_NUMBER + 1);

ACCOUNT                      BAL
-------------------- -----------
Salary                100,000.00
Capital_gains          15,000.00
Net                    61,382.80
Tax                    35,202.36
Interest               18,414.84
Iteration Count            27.00
 
ITERATION_NUMBER
Returns an integer representing the completed iteration through the model rules. The ITERATION_NUMBER function returns 0 during the first iteration. For each subsequent iteration, it returns the equivalent of  iteration_number plus one. ITERATION_NUMBER
CREATE OR REPLACE VIEW sales_view_ref AS
SELECT country_name country, prod_name prod, calendar_year year, SUM(amount_sold) sale, COUNT(amount_sold) cnt
FROM sales, times, customers, countries, products
WHERE sales.time_id = times.time_id
AND sales.prod_id = products.prod_id
AND sales.cust_id = customers.cust_id
AND customers.country_id = countries.country_id
AND (customers.country_id = 52779 OR customers.country_id = 52776)
AND (prod_name = 'Standard Mouse' OR prod_name = 'Mouse Pad')
GROUP BY country_name,prod_name,calendar_year;

SELECT * FROM sales_view_ref ORDER BY 1,3,2;

SELECT country, prod, year, s
FROM sales_view_ref
MODEL
  PARTITION BY (country)
  DIMENSION BY (prod, year)
  MEASURES (sale s)
  IGNORE NAV
  UNIQUE DIMENSION
  RULES UPSERT SEQUENTIAL ORDER ITERATE(2) (
    s['Mouse Pad', 2011 + ITERATION_NUMBER] = s['Mouse Pad', 2008 + ITERATION_NUMBER])
ORDER BY country, prod, year;

COUNTRY  PROD                  YEAR        S
-------- -------------------- ----- --------
France   Mouse Pad             1998  2509.42
France   Mouse Pad             1999  3678.69
France   Mouse Pad             2000  3000.72
France   Mouse Pad             2001  3269.09
France   Mouse Pad             2011        0
France   Mouse Pad             2012        0
France   Standard Mouse        1998  2390.83
France   Standard Mouse        1999  2280.45
France   Standard Mouse        2000  1274.31
France   Standard Mouse        2001  2164.54
Germany  Mouse Pad             1998  5827.87
Germany  Mouse Pad             1999  8346.44
Germany  Mouse Pad             2000  7375.46
Germany  Mouse Pad             2001  9535.08
Germany  Mouse Pad             2011        0
Germany  Mouse Pad             2012        0
Germany  Standard Mouse        1998  7116.11
Germany  Standard Mouse        1999  6263.14
Germany  Standard Mouse        2000  2637.31
Germany  Standard Mouse        2001  6456.13
 
MODEL CLAUSE
This clause is used only in the model_clause of a SELECT statement and then only on the right-hand side of a model rule. It yields a value for a cell in a measure column previously defined in the model_clause MEASURE_COLUMN[<condition>,<expression>]
AGGREGATE_FUNCTION[<condition>,<expression>]
AGGREGATE_FUNCTION[<column_for_loop>]
ANALYTIC_FUNCTION
SELECT country,prod,year,s
FROM sales_view_ref
MODEL
  PARTITION BY (country)
  DIMENSION BY (prod, year)
  MEASURES (sale s)
  IGNORE NAV
  UNIQUE DIMENSION
  RULES UPSERT SEQUENTIAL ORDER (
    s[prod='Mouse Pad', year=2000] =
    s['Mouse Pad', 1998] + s['Mouse Pad', 1999],
    s['Standard Mouse', 2001] = s['Standard Mouse', 2000]
)
ORDER BY country, prod, year;

COUNTRY  PROD            YEAR        S
-------- ------------   ----- --------
France   Mouse Pad       1998  2509.42
France   Mouse Pad       1999  3678.69
France   Mouse Pad       2000  6188.11
France   Mouse Pad       2001  3269.09
France   Standard Mouse  1998  2390.83
France   Standard Mouse  1999  2280.45
France   Standard Mouse  2000  1274.31
France   Standard Mouse  2001  1274.31
Germany  Mouse Pad       1998  5827.87
Germany  Mouse Pad       1999  8346.44
Germany  Mouse Pad       2000 14174.31
Germany  Mouse Pad       2001  9535.08
Germany  Standard Mouse  1998  7116.11
Germany  Standard Mouse  1999  6263.14
Germany  Standard Mouse  2000  2637.31
Germany  Standard Mouse  2001  2637.31
 
PRESENTNNV
Returns expr1 when, prior to the execution of the model_clause, cell_reference exists and is not null. Otherwise it returns expr2. PRESENTNNV(<cell_reference>, <expression1>, <expression2>)
col s format 99999.99

SELECT country, prod, year, s
FROM sales_view_ref
MODEL
  PARTITION BY (country)
  DIMENSION BY (prod, year)
  MEASURES (sale s)
  IGNORE NAV
  UNIQUE DIMENSION
  RULES UPSERT SEQUENTIAL ORDER (
    s['Mouse Pad', 2002] = PRESENTNNV(s['Mouse Pad', 2002], s['Mouse Pad', 2002], 10))
ORDER BY country, prod, year;

COUNTRY   PROD             YEAR          S
--------- --------------- ----- ----------
France    Mouse Pad        1998    2509.42
France    Mouse Pad        1999    3678.69
France    Mouse Pad        2000    3000.72
France    Mouse Pad        2001    3269.09
France    Mouse Pad        2002      10.00
France    Standard Mouse   1998    2390.83
France    Standard Mouse   1999    2280.45
France    Standard Mouse   2000    1274.31
France    Standard Mouse   2001    2164.54
Germany   Mouse Pad        1998    5827.87
Germany   Mouse Pad        1999    8346.44
Germany   Mouse Pad        2000    7375.46
Germany   Mouse Pad        2001    9535.08
Germany   Mouse Pad        2002      10.00
Germany   Standard Mouse   1998    7116.11
Germany   Standard Mouse   1999    6263.14
Germany   Standard Mouse   2000    2637.31
Germany   Standard Mouse   2001    6456.13
 
PRESENTV
Returns expr1 when, prior to the execution of the model_clause, cell_reference exists. Otherwise it returns expr2. PRESENTV(<cell_reference>, <expression1>, <expression2>)
conn sh/sh@pdbdev

col country format a20
col prod format a20
col s format 99999.99

SELECT country, prod, year, s
FROM sales_view_ref
MODEL
  PARTITION BY (country)
  DIMENSION BY (prod, year)
  MEASURES (sale s)
  IGNORE NAV
  UNIQUE DIMENSION
  RULES UPSERT SEQUENTIAL ORDER (
    s['Mouse Pad', 2001] = PRESENTV(s['Mouse Pad', 2000], s['Mouse Pad', 2000], 0))
ORDER BY country, prod, year;

COUNTRY   PROD             YEAR          S
--------- --------------- ----- ----------
France    Mouse Pad         1998   2509.42
France    Mouse Pad         1999   3678.69
France    Mouse Pad         2000   3000.72
France    Mouse Pad         2001   3000.72
France    Standard Mouse    1998   2390.83
France    Standard Mouse    1999   2280.45
France    Standard Mouse    2000   1274.31
France    Standard Mouse    2001   2164.54
Germany   Mouse Pad         1998   5827.87
Germany   Mouse Pad         1999   8346.44
Germany   Mouse Pad         2000   7375.46
Germany   Mouse Pad         2001   7375.46
Germany   Standard Mouse    1998   7116.11
Germany   Standard Mouse    1999   6263.14
Germany   Standard Mouse    2000   2637.31
Germany   Standard Mouse    2001   6456.13
 
PREVIOUS
Used only in the model_clause of the SELECT statement and then only in the ITERATE ... [ UNTIL ] clause of the model_rules_clause. It returns the value of cell_reference at the beginning of each iteration. PREVIOUS (<cell_reference>)
SELECT dim_col, cur_val, num_of_iterations
FROM (SELECT 1 AS dim_col, 10 AS cur_val FROM dual)
MODEL
  DIMENSION BY (dim_col)
  MEASURES (cur_val, 0 num_of_iterations)
  IGNORE NAV
  UNIQUE DIMENSION
  RULES ITERATE (1000) UNTIL (PREVIOUS(cur_val[1]) - cur_val[1] < 1)
  (
   cur_val[1] = cur_val[1]/2,
   num_of_iterations[1] = num_of_iterations[1] + 1
  );

 DIM_COL  CUR_VAL NUM_OF_ITERATIONS
-------- -------- -----------------
       1     .625                 4

Related Topics
Built-in Functions
Built-in Packages
SELECT Statements
What's New In 12cR2
What's New In 18cR3

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved