## Multiple Integer Series

### Join Method

Many of the single series solutions presented earlier can be easily adapted to generate multiple series with a simple join. Here is an example of how this is done using the Integer Table Method technique.

```create table integers
( integer_value integer primary key )
organization index
;

insert into integers values ( 1 );
insert into integers values ( 2 );
insert into integers values ( 3 );
insert into integers values ( 4 );
insert into integers values ( 5 );
commit;

set null "(null)"

break on key duplicates skip 1

select
key, qty, integer_value
from
t3 left outer join integers on
( integers.integer_value <= t3.qty )
order by key, integer_value ;
```
```KEY        QTY INTEGER_VALUE
--- ---------- -------------
a   (null)     (null)

b            0 (null)

c            1             1

d            2             1
d            2             2

e            3             1
e            3             2
e            3             3
```

With the Type Constructor Expression Method technique it would look like this.

```select
key, qty, column_value as integer_value
from
t3 left outer join
table( integer_table_type(1,2,3,4,5) ) integers on
( integers.column_value <= t3.qty )
order by
key, integer_value
;
```
```KEY        QTY INTEGER_VALUE
--- ---------- -------------
a   (null)     (null)

b            0 (null)

c            1             1

d            2             1
d            2             2

e            3             1
e            3             2
e            3             3
```

In both queries note that we first generate more integers than required and then filter out the excess values via a join condition.

#### 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-12527.html]SQL Snippets: Multiple Integer Series - Join Method[/url]

• <a href="http://www.sqlsnippets.com/en/topic-12527.html">SQL Snippets: Multiple Integer Series - Join Method</a>