SQL Snippets ™: Tutorials for Oracle Developers

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.