CODEWITHSUNDEEP
sqloracle-databasecommon-table-expression
Sergey Menshov
Sergey Menshov

Reputation: 3906

ORACLE (11.2.0.1.0) - Recursive CTE with a date expression

The right answer on the following question:

Question

I have a table

CREATE TABLE test(
  from_date date,
  to_date date
);

INSERT INTO test(from_date,to_date)
--VALUES('20171101','20171115');
VALUES(TO_DATE('20171101','YYYYMMDD'),TO_DATE('20171115','YYYYMMDD'));

The following query in Oracle return only one row (expected 15 rows)

WITH dateCTE(from_date,to_date,d,i) AS(
  SELECT from_date,to_date,from_date AS d,1 AS i
  FROM test

  UNION ALL

  SELECT from_date,to_date,d+INTERVAL '1' DAY,i+1
  FROM dateCTE
  WHERE d<to_date
)
SELECT d,i
FROM dateCTE

SQL Fiddle - http://sqlfiddle.com/#!4/36907/8

For test I changed the condition to i<10

WITH dateCTE(from_date,to_date,d,i) AS(
  SELECT from_date,to_date,from_date AS d,1 AS i
  FROM test

  UNION ALL

  SELECT from_date,to_date,d+INTERVAL '1' DAY,i+1
  FROM dateCTE
  --WHERE d<to_date
  WHERE i<10 -- exit condition
)
SELECT d,i
FROM dateCTE

And get the next result

| D          | I  |
|------------|----|
| 2017-11-01 |  1 |
| 2017-10-31 |  2 |
| 2017-10-30 |  3 |
| 2017-10-29 |  4 |
| 2017-10-28 |  5 |
| 2017-10-27 |  6 |
| 2017-10-26 |  7 |
| 2017-10-25 |  8 |
| 2017-10-24 |  9 |
| 2017-10-23 | 10 |

Why do this recursive query returned bad result in Oracle?

SQL Fiddle - http://sqlfiddle.com/#!4/36907/5

I ran a similar query in SQLServer and I get the right result

WITH dateCTE(from_date,to_date,d,i) AS(
  SELECT from_date,to_date,from_date AS d,1 AS i
  FROM test

  UNION ALL

  SELECT from_date,to_date,DATEADD(DAY,1,d),i+1
  FROM dateCTE
  WHERE d<to_date
)
SELECT d,i
FROM dateCTE

The right result

d           i
2017-11-01  1
2017-11-02  2
2017-11-03  3
2017-11-04  4
2017-11-05  5
2017-11-06  6
2017-11-07  7
2017-11-08  8
2017-11-09  9
2017-11-10  10
2017-11-11  11
2017-11-12  12
2017-11-13  13
2017-11-14  14
2017-11-15  15

Why it doesn't work in Oracle? What alternative variants can you suggest? Thank you!

Screen shots from a real system:

enter image description here

enter image description here

enter image description here

Upvotes: 5

Views: 1036

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

It's working for me in Oracle 11.2 Enterprise. Your SQL fiddle, however, plainly shows that it doesn't in every Oracle version.

I remember I had similar problems with dates in recursive queries. So there is a bug, that has been fixed in newer versions. It is very likely, your Oracle version has this bug.

A workaround: Get only the integer offsets from the recursive query and add them to the from_date afterwards:

WITH dateCTE(from_date, i, iend) AS
(
  SELECT from_date, 1 AS i, to_date - from_date as iend
  FROM test
  UNION ALL
  SELECT from_date, i + 1, iend
  FROM dateCTE
  WHERE i <= iend
)
, dates as (select i, from_date + i - 1 as d from dateCTE)
SELECT d, i
FROM dates;

Upvotes: 1

Mehdi Ghasri
Mehdi Ghasri

Reputation: 498

If you want to have a sequential from-date to to-date, Use such this select:

SELECT  DATE '2017-11-01' + LEVEL - 1 AS D, LEVEL AS I
FROM DUAL
CONNECT BY LEVEL <= DATE '2017-11-15' - DATE '2017-11-01' + 1;

Upvotes: 2

Related Questions