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 || '* '