This section presents tutorials on the MODEL clause of the SELECT command. Introduced in Oracle 10g, the MODEL clause is a powerful feature that gives you the ability to change any cell in the query's result set using data from any other cell (similar to the way a spreadsheet works). It also adds procedural features to SQL previously available only through PL/SQL calls.
For example, with MODEL you can take a simple table like this
KEY GROUP_1 GROUP_2 DATE_VAL NUM_VAL
------ ---------- ---------- ---------- -------
1 A a1 2005-01-01 100
2 A a2 2005-06-12 200
3 A a3 300
4 B a1 2006-02-01
5 B a2 2006-06-12 300
6 B a3 2005-01-01 100
7 C a1 2006-06-12 100
8 C a2
9 a1 2005-02-01 200
10 a2 2005-02-01 800
and, with a single command, create a report containing ad-hoc totals like this.
set null ""
select
case when key like 'Total%' then key else null end as total ,
group_1 ,
group_2 ,
num_val
from
t
model
dimension by
(
cast(key as varchar2(20)) as key ,
nvl( group_1, 'n/a' ) as group_1 ,
nvl( group_2, 'n/a' ) as group_2
)
measures( num_val )
rules
( num_val[ 'Total 1 - A + C', null, null ] =
sum(num_val)[any,group_1 in ('A','C'),any]
,
num_val[ 'Total 2 - A + a2', null, null ] =
sum(num_val)[any,'A',any] +
sum(num_val)[any,group_1 <> 'A','a2']
,
num_val[ 'Total 3 - n/a', null, null ] =
sum(num_val)[any,'n/a',any]
,
num_val[ 'Total 4 - a1 + a3', null, null ] =
sum(num_val)[any,any,group_2 in ('a1','a3')]
)
order by
group_1 ,
group_2 ,
total nulls first
;
TOTAL GROUP_1 GROUP_2 NUM_VAL
-------------------- ---------- ---------- -------
A a1 100
A a2 200
A a3 300
B a1
B a2 300
B a3 100
C a1 100
C a2
n/a a1 200
n/a a2 800
Total 1 - A + C 700
Total 2 - A + a2 1700
Total 3 - n/a 1000
Total 4 - a1 + a3 800
You can also use MODEL'S procedural features to produce results that are difficult, inefficient, or impossible to do with a non-MODEL SELECT command. Here is an example.
set null "(null)"
column string format a40
select group_1, substr( string, 2 ) as string
from t
where num_val is not null
model
return updated rows
partition by ( group_1 )
dimension by ( row_number() over (partition by group_1 order by num_val) as position )
measures ( cast( num_val as varchar2(65) ) as string ) -- Note 1
rules
upsert
iterate( 6 )
until ( presentv(string[iteration_number+2],1,0) = 0 )
( string[0] = string[0] || ',' || string[iteration_number+1] )
order by group_1 ;
GROUP_1 STRING ---------- ---------------------------------------- A 100,200,300 B 100,300 C 100 (null) 200,800
(This last technique is explained fully in another section of SQL Snippets at Rows to String: MODEL Method 1.)
Though powerful, the MODEL clause is also somewhat complex and this can be intimidating when you read about it for the first time. The tutorials to follow will therefore present very simple MODEL examples to help you quickly become comfortable with its many features.
Before continuing it is important to know that everything in the MODEL clause is evaluated after all other clauses in the query, except for SELECT DISTINCT and ORDER BY. Knowing this will help you better understand the examples in this section's tutorials.