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! |
3 responses so far ↓
eps // April 9, 2009 at 12:34 am
oracle is well rubbish
User // December 8, 2009 at 3:02 am
Doesn’t work, just returns 1 row.
User // December 8, 2009 at 3:24 am
This:
SELECT 1 just_a_column
FROM dual
GROUP BY CUBE(1,2,3,4,5,6,7,8,9)
Also returns 1 row…