sqloracle-databaseconnect-bysubquery
how to use connect by method dynamically
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.
Solution
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
)
- Math.Sin() gives incorrect value
- How to run my python script when the sunOS is start booting
- Express-session: not resetting cookie expiration on each request
- Getting a stack overflow exception when normalizing a vector
- Edit default summary function in R gives error for multiple variables
- What was a For loop? Why isn't it needed in R?
- How to use download button in shiny and save results in various formats (csv, texte, pdf, spss...)?
- Why are there two assignment operators, `<-` and `->` in R?
- lm()$assign: what is it?
- How to get the value of list(...) in R and S functions
- Design matrix for MLM from library(lme4) with fixed and random effects
- how to generate elements not included in my sample
- Create a matrix with gradually changing values without a for loop
- Emacs ESS and S-plus ( S+ ) 8.1 compatability
- How to lag date-index in a time-series in R?
- Nonlinear regression in R / S
- Calling R from S-Plus?