Oracle SQL*Plus Pocket Reference, 3rd Edition

Modeling Data

Oracle Database 10g features a powerful new clause, called MODEL, which enables spreadsheet-like calculations from a SELECT statement. It creates a multidimensional array in which cells can be referenced by dimension values.

MODEL Clause Syntax

The MODEL clause appears in the following position in a SELECT statement:

SELECT
...
GROUP BY
HAVING
MODEL model_clause
ORDER BY ...

The syntax of the MODEL clause is:

MODEL model_clause

model_clause ::= [options] [return]
   [reference_models]  main_model

options ::= [{IGNORE | KEEP} NAV]
   [UNIQUE {DIMENSION | SINGLE REFERENCE}]
return   ::= RETURN {UPDATED | ALL} ROWS

reference_models  ::=
   reference_model [reference_model...]

reference_model ::= REFERENCE   model_name
   ON (subquery)  model_columns [options]

model_columns ::= [partition [alias]]
   DIMENSION BY (column[,  column...])
   MEASURES (column[,  column...])

partition ::= PARTITION BY (column[,  column...])

column ::= {column_name  |  expression} [AS  alias]

main_model ::= [MAIN  model_name]
   model_columns [options]   rules

rules ::=
   [RULES [UPSERT | UPDATE]
      [{AUTOMATIC | SEQUENTIAL} ORDER]]
   [ITERATE (count)
      [UNTIL (termination_condition)]]
   (rule[,  rule...])

rule ::= [UPSERT | UPDATE]  cell_reference
   [ordering] =  expression

cell_reference ::=
   measure[multi_column_for_loop
          |dimension_indexes]

multi_column_for_loop ::=
   FOR (dimension[,  dimension...])
   IN (dimension_values)

dimension_values ::= (
   {subquery  |  dimension_value
   [, dimension_value...]})

dimension_value ::= (literal[,  literal...])

dimension_indexes ...