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

```

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