Integer Series Generators

MODEL Method

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


 



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-11979.html]SQL Snippets: Integer Series Generators - MODEL Method[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-11979.html">SQL Snippets: Integer Series Generators - MODEL Method</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Integer Series Generators - MODEL Method
  • URL (href): http://www.sqlsnippets.com/en/topic-11979.html

Revision Notes

Date Category Note
2007-03-31 Revision Content relating to descending series and the WHERE 1=2 clause was added.
2007-08-18 Revision Clarified the "WHERE 1=2" section. Added the "RETURN UPDATED ROWS" section.