X

Celebrating the joy and power of Oracle SQL with the Oracle Developer Advocate team

  • April 20, 2015

Converting Common Table Expressions from SQL Server to Oracle

Chris Saxon
Developer Advocate

A colleague recently posed the following question: How do I convert the following SQL Server query to Oracle?

with cte as ( 
select dateadd(mm, -1, convert(date, getdate())) as dt
union all
select dateadd(d,1,cte.dt)
from cte
where dateadd(d,1,cte.dt) <= getdate()
)
select * from cte;

This is a great question. It's a trivial query that demonstrates the difficulty of obtaining "database independence". Most aspects of it need updating for it to work in Oracle. Let's break it down.

The first (and most important!) question is: What is this query doing?

My colleague stated this is used to dynamically add dates to a query - i.e. it's a row generator, in this case for dates. Digging further, we can see that it uses common table expressions (aka CTEs) to generate the values.

getDate() returns the current date. dateAdd adds the interval of time specified in the first parameter to a date. "mm" is month, so we're subtracting one month from the current date. This gives us our base condition - today's one month ago. So on 1st Feb this returns 1st Jan.

The recursive part of the query is adding one day ("d") for each row in the set, up to the current date.

We now have the goal: write a query that generates all the dates from one month ago until today.

Now that we've established purpose, we can start digging into the detail.

The first issue is the initial select doesn't have a from clause. While is valid in SQL Server, it's not in Oracle. You always have to specify a from clause. We only want the query to return one row, so we can put dual to good use:

with cte as ( 
select dateadd(mm, -1, convert(date, getdate())) as dt
from dual
union all
select dateadd(d,1,cte.dt)
from cte
where dateadd(d,1,cte.dt) <= getdate()
)
select * from cte;

The next step is to fetch the current date. We need to replace those getDate() calls with sysdate. The SQL Server version also has a convert(date, getDate()) function, which returns the date with no time component. The equivalent in Oracle is trunc(). Plugging these in gives:

with cte as ( 
select dateadd(mm, -1, trunc(sysdate)) as dt
from dual
union all
select dateadd(d,1,cte.dt)
from cte
where dateadd(d,1,cte.dt) <= sysdate
)
select * from cte;

We're getting closer. Next step is to do the date arthimetic. Oracle doesn't have a dateAdd function. Instead it goes with the (much easier IMO) approach of allowing you to add days to dates directly with standard arithmetic, e.g. date + days. The dateAdd function differs when it comes to months though. It doesn't add or subtract 30 days, it changes the date to the equivalent day in the target month. For example, if subtracting one month 1 March the new date will be 1 February. At the end of the month it goes to the last day on the previous month however - e.g. from 31 March it will go to 28 February or 29 on leap years. To get this functionality in Oracle we use add_months(date, number).

Now we have:

with cte as ( 
select add_months(trunc(sysdate), -1) as dt
from dual
union all
select cte.dt+1
from cte
where cte.dt+1 < sysdate
)
select * from cte;

We're getting close, but executing this gives the following error:

ORA-32039: recursive WITH clause must have column alias list 

In Oracle, you need to specify the columns that form the recursive table in the table name. Putting it all together gives us the following query:

with cte (dt) as ( 
select add_months(trunc(sysdate), -1) as dt
from dual
union all
select cte.dt+1
from cte
where cte.dt+1 < sysdate
)
select * from cte;

To port our simple 7 line query from SQL Server to Oracle we've had to make 8 changes to the query! So much for database independence!

Join the discussion

Comments ( 8 )
  • guest Monday, April 20, 2015

    I don't support database independence but I'm not sure the example provided is necessarily a good one. The original query is very dependent on SQL Server which is why it takes 8 changes to port it to Oracle. Database independence proponents avoid using vendor specific features, code, functions, etc. to avoid locking the application to a specific vendor which will supposedly make it easier to switch databases.

  • Chris Saxon Tuesday, April 21, 2015

    I agree it's an extreme example. And there may be ways to rewrite it in SQL Server to make it less platform dependent.

    That said, it's a common use case (generating a list of dates) which includes other common features:

    - Date arithmetic
    - Date formatting
    - Selecting functions without a from
    - CTEs

    Separately these are all frequently used, so unless people are vigilant when trying to be database independent I suspect you'll hit at least some of these issues.

    I'd be interested to see if there's a way to write this in SQL Server that can be ported directly to Oracle with no changes or additional code needed; I'm not familiar enough with SQL Server to determine this though.

  • guest Tuesday, April 21, 2015

    Like you, I am an Oracle guy so I don't know if there is a way to write it in SQL Server that would easily port to Oracle. Assuming there is no database independent way to write it in SQL Server I think developers would write the functionality in Java or some other language and generate the dates outside of the database. That would be seen as a better approach since changing databases won't affect that particular functionality.

  • Chris Saxon Tuesday, April 21, 2015

    Coding it in the app is so much more effort though! ;)

    It's likely to perform worse in the long run too...

  • Enrique Aviles Tuesday, April 21, 2015

    I can't agree more. That highlights yet another drawback of the database independent approach. Developers will end up writing more code which translates into more bugs. Additionally, the do-it-yourself approach can lead to poor performance and scalability. The benefits of being database dependent outweigh any alleged benefits of being database independent.

  • guest Tuesday, March 1, 2016

    good

  • kirby Friday, July 6, 2018
    If database independence isn't a worry, then I suggest to use Oracle's handy Connect By clause:

    Select dt + level - 1 As dt
    From (Select add_months(trunc(sysdate), -1) As dt From dual)
    Connect By dt + level - 1 < sysdate;
  • Chris Saxon Friday, July 6, 2018
    Yep, connect by works too. And Oracle Database only supports recursive with from 11.2. So you need to use it on earlier versions!
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.