<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Creating Dynamic Lists of Dates or Numbers in a Query

Published on
10,221 Points
3,421 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

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Join & Write a Comment

This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.
Other articles by this author

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month