Oracle Tip – Generate “N” rows of dates or numbers serially

Sometimes, you would have a situation where you need to generate a table with N number of rows that you can use as the basis to join and restrict other tables.

For example, to get the 100 dates starting from a certain date as rows, a typical technique is to select ROWNUM from all_objects to generate 100 rows and come up with dates based on that. However, this technique would not work if the number of rows you need is huge. Say you needed 50,000 rows generated but your database does not have 50,000 objects, the other techniques are needed.

This is a simple yet powerful tip that even some seasoned database professionals are not aware of


SELECT ROWNUM seq
FROM
(
SELECT 1
 FROM dual
 CONNECT BY LEVEL <= 100
)

Would generate 100 rows

Seq
1
2
3
.
.
100

Expanding on the above example, to get the 100 dates from today, this would work

WITH days
AS
(
 SELECT ROWNUM seq
 FROM (SELECT 1
       FROM dual
       CONNECT BY LEVEL <= 100)
)
SELECT SYSDATE + seq
FROM days

Returns

8/21/2012
8/22/2012
8/23/2012
8/24/2012
8/25/2012
….
….
11/28/2012

Now, only your imagination is the limit on how this trick can be used.

Advertisements

One thought on “Oracle Tip – Generate “N” rows of dates or numbers serially

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s