This tutorial explains how the Rows to String solution presented in MODEL Method 3 works. Note: If you are unfamiliar with Oracle's MODEL feature you should review the tutorials at SQL Features Tutorials: MODEL Clause tutorial before proceeding.
The basic solution in the preceding tutorial looked like this.
set null "(null)"
select group_key, substr( string, 2 ) as string
from t
where 1=2
model
reference r
on
(
select
row_number() over (order by val) as val_number ,
group_key ,
val
from t
)
dimension by( val_number )
measures ( group_key, val )
main t_main
dimension by( cast(null as integer) as i, group_key )
measures ( cast(null as varchar2(65)) as string ) -- note 1
rules
upsert
iterate( 1000000 ) -- note 2
until ( presentv(r.val[iteration_number+1],1,0) = 0 )
(
string
[ for i from 1 to sign(iteration_number) increment 1,
r.group_key[iteration_number]
] = string[cv(),cv()] || ',' || r.val[iteration_number]
)
order by 1 ;
GROUP_KEY STRING ---------- --------------- Group 1 a Group 2 a,b Group 3 a,b,c Group 4 a,a,b,b Group 5 a,b,d,e, Group 6 (null) Group 7 , Group 8 a,a,b,b,, (null) (null)
Notes:
- 65 = v * ng + (ng-1)
- v = maximum length of VAL values, i.e. 10
- ng = maximum number of VALs with the same GROUP_KEY, i.e. 6
- nt = total number of rows in T
- use any number >= nt + 1
To understand how this query works we will look at intermediate steps in its processing. First, let's see what the query does without the REFERENCE or RULES clauses.
select group_key, string
from t
where 1=2
model
main t_main
dimension by( cast(null as integer) as i, group_key )
measures ( cast(null as varchar2(65)) as string )
()
order by 1 ;
no rows selected
This gives us an empty result set.
We do not need any rows at this point because the RULES clause to follow
will create all the required rows for us.
The
1=2
condition is included to prevent the DUAL row from creating
an extraneous row of nulls in the end result.
Now let's examine the REFERENCE model query.
select row_number() over (order by val) as val_number , group_key , val from t order by val ;
VAL_NUMBER GROUP_KEY VAL
---------- ---------- ----------
1 Group 1 a
2 Group 8 a
3 Group 5 a
4 Group 3 a
5 Group 2 a
6 Group 8 a
7 Group 4 a
8 Group 4 a
9 Group 5 b
10 Group 4 b
11 Group 4 b
12 Group 8 b
13 Group 8 b
14 Group 3 b
15 Group 2 b
16 Group 3 c
17 Group 5 d
18 Group 5 e
19 Group 8 (null)
20 Group 8 (null)
21 Group 7 (null)
22 Group 7 (null)
23 Group 6 (null)
24 (null) (null)
25 Group 5 (null)
As you can see, it returns two base table columns plus a control column called VAL_NUMBER, which provides a dimension key for each row.
Now let's add the RULES clause. We will change the ITERATION clause slightly so we can demonstrate what happens in each iteration of the RULES loop.
variable v_total_iterations number
execute :v_total_iterations := 1
PL/SQL procedure successfully completed.
select group_key, string
from t
where 1=2
model
reference r
on
(
select
row_number() over (order by val) as val_number ,
group_key ,
val
from t
)
dimension by( val_number )
measures ( group_key, val )
main t_main
dimension by( cast(null as integer) as i, group_key )
measures ( cast(null as varchar2(65)) as string ) -- note 1
rules
upsert
iterate( 1000000 ) -- note 2
until ( iteration_number + 1 = :v_total_iterations )
(
string
[ for i from 1 to sign(iteration_number) increment 1,
r.group_key[iteration_number]
] = string[cv(),cv()] || ',' || r.val[iteration_number]
)
order by 1 ;
no rows selected
This first iteration is uneventful. We deliberately do nothing in this iteration to prevent an extraneous row from appearing in the end result when the reference model is empty. This is required because ITERATE always performs at least one iteration, regardless of the UNTIL condition. Since there is no way to stop ITERATE from looping when we have an empty reference model we instead allow it to loop at least once and do nothing in the first iteration.
The
for i from 1 to sign(iteration_number) increment 1
logic ensures that no cell is created during the first iteration,
i.e. when ITERATION_NUMBER is 0.
When ITERATION_NUMBER is 1 or greater this logic always sets dimension "i" to "1".
Now let's see what happens after nine iterations.
execute :v_total_iterations := 9 /
GROUP_KEY STRING ---------- --------------- Group 1 ,a Group 2 ,a Group 3 ,a Group 4 ,a,a Group 5 ,a Group 8 ,a,a
In the first nine iterations one row is created for each distinct GROUP_KEY value and all the "a" VAL values are appended to the appropriate STRING column. The leading "," will be stripped off later.
Iterations 10 to 16 append all the "b" values to the appropriate STRINGs.
execute :v_total_iterations := 16 /
GROUP_KEY STRING ---------- --------------- Group 1 ,a Group 2 ,a,b Group 3 ,a,b Group 4 ,a,a,b,b Group 5 ,a,b Group 8 ,a,a,b,b
Now let's use the original ITERATE clause.
select group_key, string
from t
where 1=2
model
reference r
on
(
select
row_number() over (order by val) as val_number ,
group_key ,
val
from t
)
dimension by( val_number )
measures ( group_key, val )
main t_main
dimension by( cast(null as integer) as i, group_key )
measures ( cast(null as varchar2(65)) as string ) -- note 1
rules
upsert
ITERATE( 1000000 ) -- NOTE 2
UNTIL ( PRESENTV(R.VAL[ITERATION_NUMBER+1],1,0) = 0 )
(
string
[ for i from 1 to sign(iteration_number) increment 1,
r.group_key[iteration_number]
] = string[cv(),cv()] || ',' || r.val[iteration_number]
)
order by 1 ;
GROUP_KEY STRING ---------- --------------- Group 1 ,a Group 2 ,a,b Group 3 ,a,b,c Group 4 ,a,a,b,b Group 5 ,a,b,d,e, Group 6 , Group 7 ,, Group 8 ,a,a,b,b,, (null) ,
We use 1,000,000 as the argument to ITERATE() because we do not know, in advance, how many iterations will be required. Any number can be used here, as long as it is greater than the total number of rows returned by the REFERENCE model query. The UNTIL clause controls the actual number of iterations.
In the UNTIL clause, the
PRESENTV(R.VAL[ITERATION_NUMBER+1],1,0)=0
expression will be FALSE for iterations 0 to 24.
When ITERATION_NUMBER reaches 25 there are no more values in our reference model
to process so we can stop looping.
PRESENTV(R.VAL[ITERATION_NUMBER+1],1,0)=0
will be TRUE at this point causing iterations to stop.
Finally, we apply the
substr( string, 2 )
expression to the SELECT clause to strip the leading "," characters from STRING and
produce the end result.
select group_key, substr( string, 2 ) as string
from t
where 1=2
model
reference r
on
(
select
row_number() over (order by val) as val_number ,
group_key ,
val
from t
)
dimension by( val_number )
measures ( group_key, val )
main t_main
dimension by( cast(null as integer) as i, group_key )
measures ( cast(null as varchar2(65)) as string ) -- note 1
rules
upsert
iterate( 1000000 ) -- note 2
until ( presentv(r.val[iteration_number+1],1,0) = 0 )
(
string
[ for i from 1 to sign(iteration_number) increment 1,
r.group_key[iteration_number]
] = string[cv(),cv()] || ',' || r.val[iteration_number]
)
order by 1 ;
GROUP_KEY STRING ---------- --------------- Group 1 a Group 2 a,b Group 3 a,b,c Group 4 a,a,b,b Group 5 a,b,d,e, Group 6 (null) Group 7 , Group 8 a,a,b,b,, (null) (null)
Last, but not least, whenever working with UPSERT and UPSERT ALL rules we should always verify that our rules do not introduce unwanted rows when the result set should really be empty. Below we see that MODEL Method 3 passes the test.
set feedback on
select group_key, substr( string, 2 ) as string
from t
where 1=2
model
reference r
on
(
select
row_number() over (order by val) as val_number ,
group_key ,
val
from t
WHERE 1=2 -- forces an empty reference model
)
dimension by( val_number )
measures ( group_key, val )
main t_main
dimension by( cast(null as integer) as i, group_key )
measures ( cast(null as varchar2(65)) as string ) -- note 1
rules
upsert
iterate( 1000000 ) -- note 2
until ( presentv(r.val[iteration_number+1],1,0) = 0 )
(
string
[ for i from 1 to sign(iteration_number) increment 1,
r.group_key[iteration_number]
] = string[cv(),cv()] || ',' || r.val[iteration_number]
)
order by 1 ;
no rows selected