MODEL Clause

Ordered Rules

In this tutorial we continue exploring the RULES part of the MODEL clause with a focus on rules that use an ORDER BY clause, also known as ordered rules.

In addition to controlling the evaluation order of different rules, as we saw with sequential and automatic ordering in a previous tutorial, you can also control the assignment order of different cells targeted by a single rule. To do this we specify an ORDER BY clause after a cell reference. This is useful when a rule uses the ANY keyword or a symbolic reference on its left side. Here is an example of two very similar rules. One processes cells in ascending domain value order. The other processes cells in descending domain value order.

select
  key ,
  m1 ,
  m2
from
  t
where
  key <= 5
model
  dimension by ( key )
  measures
  (
    cast( '-' as varchar2(20) ) as m1 ,
    cast( '-' as varchar2(20) ) as m2
  )
  rules sequential order
  (
    m1[any] ORDER BY KEY ASC  = m1[ cv(key) - 1 ] || '* ' , -- rule 1
    m2[any] ORDER BY KEY DESC = m2[ cv(key) - 1 ] || '* '   -- rule 2
  )
order by
  key
;
 
   KEY M1                   M2
------ -------------------- --------------------
     1 *                    *
     2 * *                  -*
     3 * * *                -*
     4 * * * *              -*
     5 * * * * *            -*
 

To understand these results it helps to imagine how the result set appears at different stages in the processing.

Stage 1

These are the results in stage 1, right after data is selected from table T.

select
  key ,
  m1 ,
  m2
from
  t
where
  key <= 5
model
  dimension by ( key )
  measures
  (
    cast( '-' as varchar2(20) ) as m1 ,
    cast( '-' as varchar2(20) ) as m2
  )
  rules ()
order by
  key
;
 
   KEY M1                   M2
------ -------------------- --------------------
     1 -                    -
     2 -                    -
     3 -                    -
     4 -                    -
     5 -                    -
 

Stage 2

In this stage rule 1 is applied to the result set.

select
  key ,
  m1 ,
  m2
from
  t
where
  key <= 5
model
  dimension by ( key )
  measures
  (
    cast( '-' as varchar2(20) ) as m1 ,
    cast( '-' as varchar2(20) ) as m2
  )
  rules sequential order
  (
    m1[any] ORDER BY KEY ASC  = m1[ cv(key) - 1 ] || '* ' -- rule 1
  )
order by
  key
;
 
   KEY M1                   M2
------ -------------------- --------------------
     1 *                    -
     2 * *                  -
     3 * * *                -
     4 * * * *              -
     5 * * * * *            -
 

Because of the ORDER BY KEY ASC clause, cell assignments for rule 1 were performed in this order.

  • m1[1] = m1[0] || '* ' == null || '* '
  • m1[2] = m1[1] || '* ' == '* ' || '* '
  • m1[3] = m1[2] || '* ' == '* * ' || '* '
  • m1[4] = m1[3] || '* ' == '* * * ' || '* '
  • m1[5] = m1[4] || '* ' == '* * * * ' || '* '

Stage 3

In this stage rule 2 is applied.

select
  key ,
  m1 ,
  m2
from
  t
where
  key <= 5
model
  dimension by ( key )
  measures
  (
    cast( '-' as varchar2(20) ) as m1 ,
    cast( '-' as varchar2(20) ) as m2
  )
  rules sequential order
  (
    m1[any] ORDER BY KEY ASC  = m1[ cv(key) - 1 ] || '* ' , -- rule 1
    m2[any] ORDER BY KEY DESC = m2[ cv(key) - 1 ] || '* '   -- rule 2
  )
order by
  key
;
 
   KEY M1                   M2
------ -------------------- --------------------
     1 *                    *
     2 * *                  -*
     3 * * *                -*
     4 * * * *              -*
     5 * * * * *            -*
 

Because of the ORDER BY KEY DESC clause the cell assignments for rule 2 were performed in this order.

  • m2[5] = m2[4] || '* ' == '-' || '* '
  • m2[4] = m2[3] || '* ' == '-' || '* '
  • m2[3] = m2[2] || '* ' == '-' || '* '
  • m2[2] = m2[1] || '* ' == '-' || '* '
  • m2[1] = m2[0] || '* ' == null || '* '



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-11690.html]SQL Snippets: MODEL Clause - Ordered Rules[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-11690.html">SQL Snippets: MODEL Clause - Ordered Rules</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: MODEL Clause - Ordered Rules
  • URL (href): http://www.sqlsnippets.com/en/topic-11690.html