<

Creating Dynamic Lists of Dates or Numbers in a Query

Published on
10,259 Points
3,459 Views
3 Endorsements
Last Modified:
Approved
Introduction

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

Open in new window


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

Open in new window


So, using variations of these queries, you can create any number of variations to the lists of numbers. Below are a few more examples.
-- 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

Open in new window



Create a List of Dates

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
from dual
connect by level <= to_number(to_char(last_day(add_months(sysdate, -1)), 'DD'))

Open in new window




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.
3
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free