|Oracle® Database SQL Reference
10g Release 1 (10.1)
Part Number B10759-01
A model expression 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. For additional information, please refer to model_clause .
When using an aggregate function in a model expression, the argument to the function is a measure column that has been previously defined in the
model_clause. An aggregate function can be used only on the right-hand side of a model rule.
expr is itself a model expression, it is referred to as a nested cell reference.
Only one level of nesting is allowed.
A nested cell reference must be a single-cell reference.
ORDER is specified in the
model_rules_clause, a nested cell reference can be used on the left-hand side of a model rule only if the measures used in the nested cell reference remain static.
The model expressions shown below are based on the following
model_clause of the
SELECT country,prod,year,s FROM sales_view 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;
The following model expression represents a single cell reference using symbolic notation. It represents the sales of the Mouse Pad for the year 2000.
The following model expression represents a multiple cell reference using positional notation. It represents the sales of the current value of the dimension column
prod for the year 2001.
The following model expression represents an aggregate function. It represents the sum of sales of the Mouse Pad for the years between the current value of the dimension column
year less two and the current value of the dimension column
year less one.
sum(s)['Mouse Pad',year between cv()-2 and cv()-1]