SQL Snippets ™: Tutorials for Oracle Developers

In this tutorial we continue exploring the RULES part of the MODEL clause with a focus on FOR loops. FOR loops allow you to take a MODEL clause like this

select
  key ,
  flag
from
  t
model
  dimension by ( key )
  measures     ( '.' as flag )
  rules
  (
    flag[2] = 'Y' ,
    flag[4] = 'Y' ,
    flag[6] = 'Y'
  )
order by
  key
;
 

   KEY FLAG
------ ----
     1 .
     2 Y
     3 .
     4 Y
     5 .
     6 Y
     7 .
     8 .
     9 .
    10 .
 

and rewrite it like this ...

select
  key ,
  flag
from
  t
model
  dimension by ( key )
  measures     ( '.' as flag )
  rules
  (
    flag[ FOR KEY IN ( 2, 4, 6 ) ] = 'Y'
  )
order by
  key
;
 

   KEY FLAG
------ ----
     1 .
     2 Y
     3 .
     4 Y
     5 .
     6 Y
     7 .
     8 .
     9 .
    10 .
 

or this

select
  key ,
  flag
from
  t
model
  dimension by ( key )
  measures     ( '.' as flag )
  rules
  (
    flag[ FOR KEY FROM 2 TO 6 INCREMENT 2 ] = 'Y'
  )
order by
  key
;
 

   KEY FLAG
------ ----
     1 .
     2 Y
     3 .
     4 Y
     5 .
     6 Y
     7 .
     8 .
     9 .
    10 .
 

or this.

select key from t2;
 

   KEY
------
     2
     4
     6
 

select
  key ,
  flag
from
  t
model
  dimension by ( key )
  measures     ( '.' as flag )
  rules
  (
    flag[ FOR KEY IN ( SELECT KEY FROM T2 ) ] = 'Y'
  )
order by
  key
;
 

   KEY FLAG
------ ----
     1 .
     2 Y
     3 .
     4 Y
     5 .
     6 Y
     7 .
     8 .
     9 .
    10 .
 

With "FOR dimension LIKE ..." syntax you can combine strings with incrementing/decrementing values like this.

select
  key_2 ,
  flag
from
  t
model
  dimension by ( key_2 )
  measures     ( '.' as flag )
  rules
  (
    flag[ FOR KEY_2 LIKE 'T-%' FROM 2 TO 6 INCREMENT 2 ] = 'Y'
  )
order by
  key_2
;
 

KEY_2 FLAG
----- ----
T-1   .
T-10  .
T-2   Y
T-3   .
T-4   Y
T-5   .
T-6   Y
T-7   .
T-8   .
T-9   .
 

Multiple Dimensions

To specify dimension value pairs, triplets, etc. you can use a FOR loop like this.

select
  group_1 ,
  group_2 ,
  flag
from
  t
model
  dimension by ( group_1, group_2 )
  measures     ( '.' as flag )
  rules
  (
    flag
    [ FOR ( GROUP_1, GROUP_2 ) IN
      (
        ( 'A', 'a1' ) ,
        ( 'B', 'a2' ) ,
        ( 'C', 'a1' )
      )
    ] = 'Y'
  )
order by
  group_1 ,
  group_2
;
 

GROUP_1    GROUP_2    FLAG
---------- ---------- ----
A          a1         Y
A          a2         .
A          a3         .
B          a1         .
B          a2         Y
B          a3         .
C          a1         Y
C          a2         .
           a1         .
           a2         .
 

A multi-dimension FOR loop with a subquery looks like this.

select
  group_1 ,
  group_2 ,
  num_val ,
  flag
from
  t
model
  dimension by ( group_1, group_2 )
  measures     ( num_val, '.' as flag )
  rules
  (
    flag
    [ FOR ( GROUP_1, GROUP_2 ) IN
      ( SELECT GROUP_1, GROUP_2 FROM T WHERE NUM_VAL IS NULL )
    ] = 'Y'
  )
order by
  group_1 ,
  group_2
;
 

GROUP_1    GROUP_2    NUM_VAL FLAG
---------- ---------- ------- ----
A          a1             100 .
A          a2             200 .
A          a3             300 .
B          a1                 Y
B          a2             300 .
B          a3             100 .
C          a1             100 .
C          a2                 Y
           a1             200 .
           a2             800 .
 

FOR versus IN

It is important to note that a dimension reference with a FOR loop like FOR KEY IN ( 2, 4, 6 ) differs from one with an IN expression like KEY IN ( 2, 4, 6 ). This is explained further in the Missing Cells - UPSERT tutorial.