Monday, 8 December 2008

A query which generates a sequence in Oracle

Sometimes for some reason, you may need a query in Oracle
which can generate a sequence of numbers or dates.
I found the following to be an elegant way of doing it:

--count from 1 to 10
SELECT rownum
FROM dual
CONNECT BY LEVEL <= 10

--even numbers from from 2 to 20
SELECT rownum*2
FROM dual
CONNECT BY LEVEL <= 10

--get the previous 100 days
SELECT trunc(sysdate) - rownum day
FROM dual
CONNECT BY LEVEL <= 100

No comments:

Post a comment