O'Reilly logo

Oracle SQL*Plus Pocket Reference, 3rd Edition by Jonathan Gennick

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required