SQL: Generating Series of Numbers in Oracle

 

Generating a series of numbers is a very common practice in SQL environments. The objectives are but not limited to generating testing data and constants to use in SQL statements. Oracle provides a number of ways to accomplish this but the most simple to me is using the CONNECT BY clause.

   1:  SELECT ROWNUM N FROM dual 
   2:  CONNECT BY LEVEL <= 20

The statement generates integers starting from 1 to 20. It can be altered in many ways according to the acquirements e.g. adding a WHERE clause to offset the series or applying a mathematical equation at N to generate different types of series. For example:

   1:  SELECT (ROWNUM * 0.02 + 3) "N" FROM dual 
   2:  WHERE LEVEL > 10 
   3:  CONNECT BY LEVEL <= 20

Another objective of series is the requirement of constants in the SQL statements. For example I used constants of week days (1-7) and number of hours in a day (1-24) to generate some statistics by hour by day of week.

   1:  SELECT DECODE (DN, 
   2:       2, ‘MON ‘, 
   3:       3, ‘TUE ‘, 
   4:       4, ‘WED ‘, 
   5:       5, ‘THU ‘, 
   6:       6, ‘FRI ‘, 
   7:       7, ‘SAT ‘, 
   8:       1, ‘SUN ‘) "Day of Week", 
   9:  Hour, 
  10:  (SELECT COUNT(*) FROM tblsupport 
  11:   WHERE tktopendate > SYSDATE – 90 
  12:   AND TO_CHAR(tktopendate, ‘D’) = DN 
  13:   AND TO_CHAR(tktopendate, ‘HH24’) = H) "Ticket Count" 
  14:  FROM 
  15:  (SELECT ROWNUM DN FROM dual 
  16:    CONNECT BY LEVEL <= 7) WeekDays, 
  17:  (SELECT ROWNUM Hour FROM dual
  18:   CONNECT BY LEVEL <= 24) Hours

This statement generates statistics data about number of tickets received during the last 3 months grouped by week day and hour.

 

 

Share this post : digg it! Facebook it! live it! reddit! technorati! yahoo!

Advertisements

4 thoughts on “SQL: Generating Series of Numbers in Oracle

  1. If you have a table with lots of rows, this is awkward but it works:

    SELECT MYNUM FROM (SELECT ROWNUM AS MYNUM FROM THEBIGTABLE) WHERE MYNUM <100

    Will give you a column "MYNUM" with 99 rows with numbers 1-99.

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