<

Creating Dynamic Lists of Dates or Numbers in a Query

Published on
10,189 Points
3,389 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
Comment
0 Comments

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Join & Write a Comment

This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Other articles by this author

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month