This tutorial demonstrates how to generate a series of integers using the MODEL clause of the SELECT command. (You can learn more about MODEL at SQL Features Tutorials: MODEL Clause.) This technique only works with Oracle versions starting at 10g. Other techniques are discussed in the tutorials listed in the menu to the left.
With this technique you can generate a series of integers starting at "1" using a query like this.
select integer_value from dual where 1=2 model dimension by ( 0 as key ) measures ( 0 as integer_value ) rules upsert ( integer_value[ for key from 1 to 10 increment 1 ] = cv(key) ) ;
INTEGER_VALUE ------------- 1 2 3 4 5 6 7 8 9 10
Chaning the INCREMENT value lets us control the difference between successive values in the series.
select integer_value from dual where 1=2 model dimension by ( 0 as key ) measures ( 0 as integer_value ) rules upsert ( integer_value[ for key from 2 to 10 INCREMENT 2 ] = cv(key) ) ;
INTEGER_VALUE ------------- 2 4 6 8 10
We can use bind variables to make the solution more generic.
variable v_first_key number variable v_last_key number variable v_increment number execute :V_FIRST_KEY := 1 execute :V_LAST_KEY := 5 execute :V_INCREMENT := 2 select key, integer_value from dual where 1=2 model dimension by ( 0 as key ) measures ( 0 as integer_value ) rules upsert ( integer_value[ for key from :V_FIRST_KEY to :V_LAST_KEY increment 1 ] = nvl2( integer_value[cv()-1], integer_value[cv()-1] + :V_INCREMENT, cv(key) ) ) ;
KEY INTEGER_VALUE ---------- ------------- 1 1 2 3 3 5 4 7 5 9
When v_last_key is NULL or less than v_first_key no rows are returned.
execute :v_first_key := 1 PL/SQL procedure successfully completed. execute :v_last_key := null PL/SQL procedure successfully completed. / no rows selected execute :v_last_key := 0 PL/SQL procedure successfully completed. / no rows selected execute :v_last_key := -5 PL/SQL procedure successfully completed. / no rows selected
Day of the Week Case Study
We can apply this technique to the day of the week scenario presented at the start of this chapter as follows.
select day_of_week , t.val from ( select day_of_week from dual where 1=2 model dimension by ( 0 as key ) measures ( 0 as day_of_week ) rules upsert ( day_of_week[ for key from 0 to 6 increment 1 ] = cv(key) ) ) i left outer join t using ( day_of_week ) order by day_of_week ;
DAY_OF_WEEK VAL ----------- ---------- 0 1 100 2 3 300 4 400 5 500 6
Gotchas
Descnending Series
If you need a descending series of integers this attempt will not work.
select integer_value from dual where 1=2 model dimension by ( 0 as key ) measures ( 0 as integer_value ) rules upsert ( integer_value[ for key FROM 3 TO 1 increment 1 ] = cv(key) ) ; no rows selected
Instead, do it this way
select integer_value from dual where 1=2 model dimension by ( 0 as key ) measures ( 0 as integer_value ) rules upsert ( integer_value[ for key from 3 to 1 DECREMENT 1 ] = cv(key) ) ORDER BY INTEGER_VALUE DESC ;
INTEGER_VALUE ------------- 3 2 1
or this way.
select integer_value from dual where 1=2 model dimension by ( 0 as key ) measures ( 0 as integer_value ) rules upsert ( integer_value[ for key from 1 TO 3 INCREMENT 1 ] = cv(key) ) ORDER BY INTEGER_VALUE DESC ;
INTEGER_VALUE ------------- 3 2 1
WHERE 1=2
It is important to note that everything in the MODEL clause is evaluated after
all other clauses in the query, except for SELECT DISTINCT and ORDER BY.
Using the WHERE 1=2
clause ensures the query starts with an empty result set
when MODEL rules are first applied to the rows returned by the SELECT ... FROM ... WHERE portion
of the query.
While it would be possible to omit the
WHERE 1=2
clause using an approach like this
select integer_value from dual model dimension by ( 1 as key ) measures ( 1 as integer_value ) rules upsert ( integer_value[ for key from 1 to 10 increment 1 ] = cv(key) ) ;
INTEGER_VALUE ------------- 1 2 3 4 5 6 7 8 9 10
this query causes the result set to always contain at least one row both before and after the MODEL rules are applied. This is not a problem for queries that always return one or more rows like this one,
select key, integer_value from dual model dimension by ( 4 as key ) measures ( 4 as integer_value ) rules upsert ( integer_value[ for key from 4 to 8 increment 1 ] = cv(key) ) ;
KEY INTEGER_VALUE ---------- ------------- 4 4 5 5 6 6 7 7 8 8
but if the code is later parameterized and the TO bound is ever null or less than the FROM bound then the query will incorrectly return 1 row instead of the required zero rows for these cases.
variable v_first_key number variable v_last_key number execute :v_first_key := 3 execute :v_last_key := 0 select key, integer_value from dual model dimension by ( :v_first_key as key ) measures ( :v_first_key as integer_value ) rules upsert ( integer_value[ for key from :v_first_key to :v_last_key increment 1 ] = cv(key) ) ;
KEY INTEGER_VALUE ---------- ------------- 3 3
RETURN UPDATED ROWS
An alternative to using WHERE 1=2
would be to instead include a RETURN UPDATED ROWS clause,
like this
select integer_value from dual model RETURN UPDATED ROWS dimension by ( 1 as key ) measures ( 1 as integer_value ) rules upsert ( integer_value[ for key from 1 TO 3 increment 1 ] = cv(key) ) ;
INTEGER_VALUE ------------- 1 2 3
select integer_value from dual model RETURN UPDATED ROWS dimension by ( 1 as key ) measures ( 1 as integer_value ) rules upsert ( integer_value[ for key from 3 TO 0 increment 1 ] = cv(key) ) ; no rows selected
but using WHERE 1=2
to ensure the query always starts with an empty set
seems like a cleaner way to work than starting with one row and then relying on
RETURN UPDATED ROWS to return that row in some cases but not others.
INCREMENT and Bind Variables
Unlike the FROM and TO bounds, we cannot use a variable in the INCREMENT value (as tested in 10g).
variable v_first_key number variable v_last_key number variable v_increment number execute :v_first_key := 1 execute :v_last_key := 9 execute :v_increment := 2 select key, integer_value from dual where 1=2 model dimension by ( 0 as key ) measures ( 0 as integer_value ) rules upsert ( integer_value[ for key from :v_first_key to :v_last_key INCREMENT :v_increment ] = cv(key) ) ; ( integer_value[ for key from :v_first_key to :v_last_key INCREMENT :v_increment ] * ERROR at line 8: ORA-32626: illegal bounds or increment in MODEL FOR loop