how to use connect by method dynamically

Question

How to achieve the following

I've got a query:

select sr.source_rel_id,
       sr.source_rel_start,
       sr.source_rel_end,
       sr.source_rel_end-sr.source_rel_start+ 1 as daycount
 from (SELECT RELEASES.REL_ID as source_rel_id,
              RELEASES.REL_START_DATE as source_rel_start,
              RELEASES.REL_END_DATE as source_rel_end
         FROM RELEASES) sr

which delivers this result:

I'm trying to create an additionally coloumn (based on the attached screenshot) filled with incrementally rising dates between the given start and end interval.

Here's what I want to accomplish:

The number on the right side shows how many records should be generated for each IDs.

Sorry for my poor english, I hope it's understandable.

Please post your data and results [as formatted text rather than images](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) in future. — Alex Poole, Dec 06 '17 at 10:41

krokodilko · Accepted Answer · 2017-12-06T17:19:04.353

This is basically a connect-by query that generates rows, for example to generate rows from 1 to 10 use:

select level as x
from dual connect by level <= 10;

|  X |
|----|
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |

in order to generate rows from 5 to 10 use:

select 5 + level - 1 as x
from dual connect by level <= 10 - 5 + 1;

|  X |
|----|
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |

In order to generate dates from 2017-01-02 to 2017-01-05 use:

select date '2017-01-02' + level - 1 as x
from dual connect by level <= date '2017-01-05' - date '2017-01-02' + 1;

|                    X |
|----------------------|
| 2017-01-02T00:00:00Z |
| 2017-01-03T00:00:00Z |
| 2017-01-04T00:00:00Z |
| 2017-01-05T00:00:00Z |

If you are using Oracle 12c then you can use LATERAL or CROSSAPPLY to run the latter generator query form many start+end values coming from source subquery or table, please consider the following example:

create table probe(
   source_rel_id int,
   source_rel_start date,
   source_rel_end date
);

insert into probe values( 1, date '2017-01-02', date '2017-01-05' );
insert into probe values( 2, date '2017-03-01', date '2017-03-15' );
insert into probe values( 3, date '2017-05-05', date '2017-05-30' );
commit;

select * from probe p
cross apply (
    select p.source_rel_start + level - 1 as my_date
    from dual connect by level <= p.source_rel_end - p.source_rel_start + 1
)

SOURCE_REL_ID SOURCE_REL_START SOURCE_REL_END   MY_DATE         
------------- ---------------- ---------------- ----------------
            1 2017/01/02 00:00 2017/01/05 00:00 2017/01/02 00:00
            1 2017/01/02 00:00 2017/01/05 00:00 2017/01/03 00:00
            1 2017/01/02 00:00 2017/01/05 00:00 2017/01/04 00:00
            1 2017/01/02 00:00 2017/01/05 00:00 2017/01/05 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/01 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/02 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/03 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/04 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/05 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/06 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/07 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/08 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/09 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/10 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/11 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/12 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/13 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/14 00:00
            2 2017/03/01 00:00 2017/03/15 00:00 2017/03/15 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/05 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/06 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/07 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/08 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/09 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/10 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/11 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/12 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/13 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/14 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/15 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/16 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/17 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/18 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/19 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/20 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/21 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/22 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/23 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/24 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/25 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/26 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/27 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/28 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/29 00:00
            3 2017/05/05 00:00 2017/05/30 00:00 2017/05/30 00:00

EDIT

What other options I've got, if I'm not having 12c? I should check the version but I remember for 11.

Just generate a series of numbers from 1 to N, where N must be greater than the highest count of numbers you want to generate for one record, in this way:

  SELECT level as xyz FROM dual
  CONNECT BY LEVEL <= 10000

and then join a result of the above query to the table in this way --> please see this demo

SELECT source_rel_id, source_rel_start, source_rel_end,
       source_rel_start + xyz - 1 As days
FROM (
  SELECT level as xyz FROM dual
  CONNECT BY LEVEL <= 10000
) x
JOIN probe p
ON xyz <= source_rel_end - source_rel_start + 1
ORDER BY source_rel_id, days

| SOURCE_REL_ID |     SOURCE_REL_START |       SOURCE_REL_END |                 DAYS |
|---------------|----------------------|----------------------|----------------------|
|             1 | 2017-01-02T00:00:00Z | 2017-01-05T00:00:00Z | 2017-01-02T00:00:00Z |
|             1 | 2017-01-02T00:00:00Z | 2017-01-05T00:00:00Z | 2017-01-03T00:00:00Z |
|             1 | 2017-01-02T00:00:00Z | 2017-01-05T00:00:00Z | 2017-01-04T00:00:00Z |
|             1 | 2017-01-02T00:00:00Z | 2017-01-05T00:00:00Z | 2017-01-05T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-01T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-02T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-03T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-04T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-05T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-06T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-07T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-08T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-09T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-10T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-11T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-12T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-13T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-14T00:00:00Z |
|             2 | 2017-03-01T00:00:00Z | 2017-03-15T00:00:00Z | 2017-03-15T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-05T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-06T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-07T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-08T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-09T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-10T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-11T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-12T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-13T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-14T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-15T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-16T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-17T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-18T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-19T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-20T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-21T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-22T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-23T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-24T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-25T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-26T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-27T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-28T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-29T00:00:00Z |
|             3 | 2017-05-05T00:00:00Z | 2017-05-30T00:00:00Z | 2017-05-30T00:00:00Z |

I've used 10000, it should be enough since 10000 days is over 27 years, so I don't think you need to generate 27 years of dates for each record, but if this is still too little, then in practice it can be a number somwhere between 1 million and 10 millions. Since rows are generated in the memory, at some point ( 1~10 millions) you will hit an out of memory error.
You can also use a subquery to calculate this limit exactly:

SELECT level as xyz FROM dual
CONNECT BY LEVEL <= (
     SELECT max(  source_rel_end - source_rel_start ) + 1
     FROM probe
)

I'll give it a try today evening. What other options I've got, if I'm not having 12c? I should check the version but I remember for 11. — imi36, Dec 06 '17 at 07:16
I've added an example how to do it in erlier versions of Oracle — krokodilko, Dec 06 '17 at 17:11
I owe you a beer. Many thanks. I've got Oracle 12c, so your solution with using cross apply worked well. — imi36, Dec 06 '17 at 22:18

score 0 · Answer 2 · answered Dec 06 '17 at 10:41

If you're still on Oracle 11g (suggested in a comment), you can do this with a connect by hierarchical query, but because you're getting the data from multiple source rows you need include a reference to a non-deterministic function (I've used `dbms_random.value but others are available...) to prevent that:

select rel_id as source_rel_id,
  rel_start_date + level - 1 as days,
  rel_end_date - rel_start_date + 1 as daycount
from releases
connect by rel_id  = prior rel_id
and prior dbms_random.value is not null
and level <= rel_end_date - rel_start_date + 1;

which gets, using just the first three rows of your sample data, 92 rows in the result set:

SOURCE_REL_ID DAYS         DAYCOUNT
------------- ---------- ----------
           68 2016-03-01         31
           68 2016-03-02         31
           68 2016-03-03         31
...
           68 2016-03-30         31
           68 2016-03-31         31
           71 2016-06-01         30
           71 2016-06-02         30
...
           71 2016-06-29         30
           71 2016-06-30         30
           73 2016-08-01         31
           73 2016-08-02         31
...
           73 2016-08-30         31
           73 2016-08-31         31

If you're on 11gR2 (or higher) you can also do this with recursive subquery factoring a.k.a a recursive CTE:

with rcte (source_rel_id, rel_end_date, days, daycount) as (
  select rel_id, rel_end_date, rel_start_date, rel_end_date - rel_start_date + 1
  from releases
  union all
  select source_rel_id, rel_end_date, days + 1, daycount
  from rcte
  where days < rel_end_date
)
select source_rel_id, days, daycount
from rcte
order by source_rel_id, days;

which gets the same result - other than being ordered explicitly, which probably isn't a bad thing.

The anchor branch of the CTE gets data from your base table and adds two extra columns: daycount which is calculated as before, and days which is the same as the start date for this first set of rows. The rel_end_date needs to be available to the recursive branch so that is included, but rel_start_date can be skipped.

The recursive branch then adds one day to days until it reaches the end date. If the days value from the previous generated row is already rel_end_date then the condition is not met so it stops, for that source row.

Finally you query only the columns you want to see from the CTE - in this case, just excluding the rel_end_date that was passed through to provide the stop condition, but which you don't actually want to see.

how to use connect by method dynamically

2 Answers2