Integer Series Generators

ROWNUM + a Big Table Method

This tutorial demonstrates how to generate a series of integers using the ROWNUM pseudocolumn and any available table with as many rows in it as the number of integers required. Other techniques are discussed in the tutorials listed in the menu to the left.

Prerequisites

Before using this solution you need to find a table with at least as many rows in it as the number of integers you need to generate. I.e. if you need a series of 10 integers then you need to find a table or view that will always have at least 10 rows in it. The data dictionary view ALL_OBJECTS is a popular choice for this method.

The Solution

Once you have identified a table with a sufficient number of rows simply select ROWNUM from it to generate the required integer series, like this.

select rownum
from   all_objects
where  rownum <= 10 ;
 
    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
 

We can apply this technique to our day of the week scenario as follows.

select
  day_of_week ,
  t.val
from
  ( select rownum - 1 as day_of_week
    from   all_objects
    where  rownum <= 7
  ) 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
 



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-11831.html]SQL Snippets: Integer Series Generators - ROWNUM + a Big Table 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-11831.html">SQL Snippets: Integer Series Generators - ROWNUM + a Big Table Method</a>

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

  • Link Text : SQL Snippets: Integer Series Generators - ROWNUM + a Big Table Method
  • URL (href): http://www.sqlsnippets.com/en/topic-11831.html