ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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;
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;
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;
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;
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;
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
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
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
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
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
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
);
