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