CODEWITHSUNDEEP
oracle-database
Basti
Basti

Reputation: 149

Trouble with Oracle Connect By and Date Ranges

Let's say I have a table with data ranges

create table ranges (id number, date_from date, date_to date);
insert into ranges values (1, to_date('01.01.2017', 'dd.mm.rrrr'), to_date('03.01.2017', 'dd.mm.rrrr'));
insert into ranges values (2, to_date('05.02.2017', 'dd.mm.rrrr'), to_date('08.02.2017', 'dd.mm.rrrr'));

and my output should by one row for every date in these ranges

id | the_date
----------------
1  | 01.01.2017
1  | 02.01.2017
1  | 03.01.2017
2  | 05.02.2017
2  | 06.02.2017
2  | 07.02.2017
2  | 08.02.2017

But connect by gives me ORA-01436 Connect by Loop

SELECT connect_by_root(id), Trunc(date_from, 'dd') + LEVEL - 1 AS the_date
FROM ranges
CONNECT BY PRIOR id = id AND Trunc(date_from, 'dd') + LEVEL - 1 <= Trunc(date_to, 'dd')
ORDER BY id, the_date

What's wrong?

Upvotes: 1

Views: 603

Answers (1)

Alex Poole
Alex Poole

Reputation: 191415

You can add a call to a non-deterministic function, e.g.

AND PRIOR dbms_random.value IS NOT NULL

So it becomes:

SELECT connect_by_root(id), Trunc(date_from, 'dd') + LEVEL - 1 AS the_date
FROM ranges
CONNECT BY PRIOR id = id
AND PRIOR dbms_random.value IS NOT NULL
AND Trunc(date_from, 'dd') + LEVEL - 1 <= Trunc(date_to, 'dd')
ORDER BY id, the_date;

CONNECT_BY_ROOT(ID) THE_DATE 
------------------- ---------
                  1 01-JAN-17
                  1 02-JAN-17
                  1 03-JAN-17
                  2 05-FEB-17
                  2 06-FEB-17
                  2 07-FEB-17
                  2 08-FEB-17

7 rows selected.

There's an explanation of why it is necessary in this Oracle Community post; that uses sys_guid() instead of dbms_random.value, but the principle is the same.

If you're on 11gR2 or higher you could use recursive subquery factoring instead:

WITH rcte (root_id, the_date, date_to) AS (
  SELECT id, date_from, date_to
  FROM ranges
  UNION ALL
  SELECT root_id, the_date + 1, date_to
  FROM rcte
  WHERE the_date < date_to
)
SELECT root_id, the_date
FROM rcte
ORDER BY root_id, the_date;

   ROOT_ID THE_DATE 
---------- ---------
         1 01-JAN-17
         1 02-JAN-17
         1 03-JAN-17
         2 05-FEB-17
         2 06-FEB-17
         2 07-FEB-17
         2 08-FEB-17

7 rows selected.

Upvotes: 3

Related Questions