Browse All Articles
> Creating Dynamic Lists of Dates or Numbers in a Query
Throughout my many years of work in IT, there have been many times when I've needed to write some SQL that required a list of numbers or dates. Originally, the only way I could achieve this was to create a table in the database. The downside of this was that it had to be maintained manually and if forgotten, could lead to functionality loss. Also not being a DBA, it was often difficult to get approval for such a table.
Create a List of Numbers
In Oracle SQL, this functionality is easily achieved by using the CONNECT BY clause
The following example will provide you with a list of numbers from 1 to 12. This is perfect for months of a year.
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 12
Another example I use this for is to create a list of percentages between 0 and 100
SELECT (LEVEL-1) COL FROM DUAL CONNECT BY LEVEL <= 101
So, using variations of these queries, you can create any number of variations to the lists of numbers. Below are a few more examples.
Create a List of Dates
-- 0 to 100 with an intival of 5
SELECT (LEVEL-1) * 5 COL FROM DUAL CONNECT BY LEVEL <= 21
-- List if the last 20 years
SELECT extract(year from sysdate) - LEVEL + 1 COL FROM DUAL CONNECT BY LEVEL <= 21
Using similar logic from above getting a list of numbers, you can apply it to dates to achieve a list of required dates. The most common reason for me to use a list of dates is to join them to a GROUP BY query that I want to use to populate a chart. The GROUP BY query may not contain all the dates in the month so if I join the list of dates to my GROUP BY query using a LEFT OUTER JOIN, my chart will always have every date in the month.
Below is a query containing all the dates from last month.
SELECT trunc(add_months(sysdate, -1), 'mon') + LEVEL - 1 ADATE
connect by level <= to_number(to_char(last_day(add_months(sysdate, -1)), 'DD'))
These tips have helped me over the past few years and I hope that someone else can get use out of them. I have found that once you master one, variations are very easily created.