Connect By Level Condition
As most of us software engineers know occasionally a situation will arise where data needs to be charted over time. However problems can arise where there are intervals for which no data exists. Connect By Level Condition could be the answer to this!
The following looks at an Oracle based solution to writing such queries.
Suppose we have a simple set of appointments similar to table 1 below.
Table 1. Events Table
We may wish to calculate the number of appointments we have each day for the last week.
A first pass at such a query might be: SELECT EventDate, Count(EventName) as NumEvents FROM tbEvents GROUP BY Event Date
This will give us the following data:
One thing you may notice is that it doesn’t have any rows for the 14th and 17th. As no data was recorded for those days, no records will be returned. This will cause problems if the goal is to chart this data per day.
Some charting tools will have the ability to fill in these missing days as points on the chart but sometime we may find ourselves in the position of having a far simpler tool available- one that perhaps can only chart the data we give it.
Oracle provides an elegant solution to this problem using the CONNECT BY LEVEL condition.
SELECT LEVEL FROM dual CONNECT BY LEVEL <= 7
will give us a list of numbers from 1 to 7.
We can alter this slightly to get a row for each day in the past week:
SELECT TO_DATE (TO_CHAR (SYSDATE - LEVEL, 'DD-MON-YYYY ' ) ,'DD-MON-YYYY' ) dates FROM DUAL CONNECT BY LEVEL<= 7
We can then join this to our original table to produce the desired result:
SELECT daterange.dates ,COUNT(eventName) FROM (SELECT TO_DATE(TO_CHAR(SYSDATE - LEVEL,'DD-MON-YYYY ') , 'DD-MON-YYYY') dates FROM DUAL CONNECTBYLEVEL<= 7 ) daterange LEFT OUTER JOIN tbEvents ON TO_DATE(TO_CHAR (tbEvents.eventDate, 'DD-MON-YYYY ' ) , 'DD-MON-YYYY' ) = daterange.dates GROUP BY daterange.dates ORDER BY daterange.dates
This will give us the table below (assuming that today is the 20– Jan – 2012)
As you can see this dataset is then much easier to chart. This query could be adapted for any missing sequential data.
Returning a Dataset from an Oracle Function
Read our next blog where we detail how to return a dataset from an Oracle function: Returning a Dataset from an Oracle Function
At Dataworks we utilise the perfect hybrid of configurable off the shelf toolsets and custom development to deliver innovative solutions – all based on leading and complementary technologies. This enables us to pinpoint where our software applications can make a real difference and deliver real benefits to your organisation.
If you would like to discuss how we can use our experience and expertise to deliver real benefits toyour business please contact us today on 051 878555051 878555 or email firstname.lastname@example.org