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]

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

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

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

  • Link Text : SQL Snippets: Multiple Integer Series - Join Method
  • URL (href): http://www.sqlsnippets.com/en/topic-12527.html