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! |

oracle is well rubbish

Doesn’t work, just returns 1 row.

This:

SELECT 1 just_a_column

FROM dual

GROUP BY CUBE(1,2,3,4,5,6,7,8,9)

Also returns 1 row…

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.