SQL Snippets ™: Tutorials for Oracle Developers

In this tutorial we explore the concept of reference models within the MODEL clause.

Until now the tutorials in this section have all dealt with a single model, called the main model. Beyond the main model you can create additional multi-dimensional arrays, called reference models, whose cells you can refer to in your main model. Reference models act like read-only lookup tables for your main model. As such, you cannot update or insert cells in a reference model.

Before we look at a reference model example note that, in addition to the table "T" we have used so far we will also use a table called "T3" which looks like this.

select * from T3;
 

KEY        NAME
---------- ---------------
a1         Alpha Squad 1
a2         Alpha Squad 2
a3         Alpha Squad 3
 

Now here is the reference model example.

select
  key, group_2, group_2_name
from
  t
model
  REFERENCE T3_REF
    ON
    (
      SELECT KEY, NAME
      FROM   T3
    )
    DIMENSION BY ( KEY )
    MEASURES     ( NAME )
  main T_MAIN
    dimension by ( key )
    measures     ( group_2, 'Undefined Group' as group_2_name )
    rules        ( group_2_name[any] = T3_REF.NAME[ GROUP_2[CV()] ] )
order by
  key
;
 

   KEY GROUP_2    GROUP_2_NAME
------ ---------- ---------------
     1 a1         Alpha Squad 1
     2 a2         Alpha Squad 2
     3 a3         Alpha Squad 3
     4 a1         Alpha Squad 1
     5 a2         Alpha Squad 2
     6 a3         Alpha Squad 3
     7 a1         Alpha Squad 1
     8 a2         Alpha Squad 2
     9 a1         Alpha Squad 1
    10 a2         Alpha Squad 2
 

Gotchas

If you try to update a reference model cell you will get an error.

select
  key, group_2, group_2_name
from
  t
model
  reference t3_ref
    on
    (
      select key, name
      from   t3
    )
    dimension by ( key )
    measures     ( name )
  main t_main
    dimension by ( key )
    measures     ( group_2, 'Undefined Group' as group_2_name )
    rules        ( T3_REF.NAME['a1'] = 'Alpha Squad 7' )
order by
  key
;
    rules        ( T3_REF.NAME['a1'] = 'Alpha Squad 7' )
                   *
ERROR at line 17:
ORA-00904: : invalid identifier