Skip Headers
Oracle® Database SQL Language Reference
11g Release 2 (11.2)

Part Number E17118-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

Model Expressions

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, refer to model_clause .

model_expression::=

Description of model_expression.gif follows
Description of the illustration model_expression.gif

When you specify a measure column in a model expression, any conditions and expressions you specify must resolve to single values.

When you specify 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.

Specifying an analytic function on the right-hand side of the model rule lets you express complex calculations directly in the model_clause. The following restrictions apply when using an analytic function in a model expression:

See Also:

"The MODEL clause: Examples" for an example of using an analytic function on the right-hand side of a model rule

When expr is itself a model expression, it is referred to as a nested cell reference. The following restrictions apply to nested cell references:

The model expressions shown below are based on the model_clause of the following SELECT statement:

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;

The following model expression represents a single cell reference using symbolic notation. It represents the sales of the Mouse Pad for the year 2000.

s[prod='Mouse Pad',year=2000]

The following model expression represents a multiple cell reference using positional notation, using the CV function. It represents the sales of the current value of the dimension column prod for the year 2001.

s[CV(prod), 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]

See Also:

CV and model_clause