How to include an effectivity date range check in hierarchical query? Such that a loop may exist if the date range is excluded, but considering the range a loop would not.
with
output_tab as (select 'P1' as output_id, 100 as output_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P2' as output_id, 200 as output_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P3' as output_id, 300 as output_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P4' as output_id, 400 as output_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P5' as output_id, 500 as output_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual),
input_tab as (select 'C8' as input_id, 'P1' as output_id, 1111 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'C9' as input_id, 'P1' as output_id, 2222 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'C12' as input_id,'P1' as output_id, 3333 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'C5' as input_id, 'P2' as output_id, 4444 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'C7' as input_id, 'P2' as output_id, 5555 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P1' as input_id, 'P2' as output_id, 100 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('09/17/2018', 'MM/DD/YYYY') as end_date from dual --ends
union all
select 'C6' as input_id, 'P3' as output_id, 9999 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'C10' as input_id, 'P3' as output_id, 1122 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'C1' as input_id, 'P4' as output_id, 6666 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'C2' as input_id, 'P4' as output_id, 7777 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'C4' as input_id, 'P4' as output_id, 8888 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P2' as input_id, 'P4' as output_id, 200 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P4' as input_id, 'P5' as output_id, 400 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P3' as input_id, 'P5' as output_id, 300 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P5' as input_id, 'P1' as output_id, 500 as input_key, to_date('09/18/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual -- starts
)
select distinct ot.output_id, ot.output_key, it.input_key, it.input_id, it.start_date, it.end_date
from output_tab ot inner join input_tab it on (ot.output_id = it.output_id)
start with ot.output_id = 'P1'
connect by it.input_key = prior ot.output_key
order by 1, 2
Above, the last union (with "-- starts") creates the loop and the query fails; however, its start date is after the ending of another entry (with "-- ends"), so I want to be able to see that as not being a loop.
This check is desired to done against configuration a user in putting into the UI prior to commiting, the heirarchy could be complicated and we want to know if a loop exists (the user may have made a mistake). Currently, the best option I'm entertaining is using a loop to check each date inidividually for the parents effectivity range, but I'm hoping there is a better way.
Thanks,
Steve
You need to add conditions to your connect by so it doesn't revisit the earlier row.
Based on the data shown, you can do this by checking that the current start_date is on or after the previous:
start_date >= prior start_date
But I suspect this is a feature of your sample data. If P1 starting 18 Sept has a child row, would its start date have to be on 18 Sept or after too? If no, you'll have to find something else to check against.
Also, starting with just P1 means you'll get more root for this than you need. You can filter this down to the first by checking the current start_date equals the minimum for the output_key:
with
output_tab as (
select 'P1' as output_id, 100 as output_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P2' as output_id, 200 as output_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P3' as output_id, 300 as output_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P4' as output_id, 400 as output_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P5' as output_id, 500 as output_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual),
input_tab as (
select 'C8' as input_id, 'P1' as output_id, 1111 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'C9' as input_id, 'P1' as output_id, 2222 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'C12' as input_id,'P1' as output_id, 3333 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'C5' as input_id, 'P2' as output_id, 4444 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'C7' as input_id, 'P2' as output_id, 5555 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P1' as input_id, 'P2' as output_id, 100 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('09/17/2018', 'MM/DD/YYYY') as end_date from dual --ends
union all
select 'C6' as input_id, 'P3' as output_id, 9999 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'C10' as input_id, 'P3' as output_id, 1122 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'C1' as input_id, 'P4' as output_id, 6666 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'C2' as input_id, 'P4' as output_id, 7777 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'C4' as input_id, 'P4' as output_id, 8888 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P2' as input_id, 'P4' as output_id, 200 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P4' as input_id, 'P5' as output_id, 400 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P3' as input_id, 'P5' as output_id, 300 as input_key, to_date('01/01/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual
union all
select 'P5' as input_id, 'P1' as output_id, 500 as input_key, to_date('09/18/2018', 'MM/DD/YYYY') as start_date, to_date('12/31/2020', 'MM/DD/YYYY') as end_date from dual -- starts
) , t as (
select ot.output_id, ot.output_key, it.input_key, it.input_id, it.start_date, it.end_date ,
min ( it.start_date ) over ( partition by ot.output_id ) mn_start
from output_tab ot inner join input_tab it on (ot.output_id = it.output_id)
)
select level, t.* from t
start with output_id = 'P1'
and mn_start = start_date
connect by input_key = prior output_key
and start_date >= prior start_date
order by 1, 2;
LEVEL OUTPUT_ID OUTPUT_KEY INPUT_KEY INPUT_ID START_DATE END_DATE MN_START
1 P1 100 1111 C8 01-JAN-2018 31-DEC-2020 01-JAN-2018
1 P1 100 3333 C12 01-JAN-2018 31-DEC-2020 01-JAN-2018
1 P1 100 2222 C9 01-JAN-2018 31-DEC-2020 01-JAN-2018
2 P2 200 100 P1 01-JAN-2018 17-SEP-2018 01-JAN-2018
2 P2 200 100 P1 01-JAN-2018 17-SEP-2018 01-JAN-2018
2 P2 200 100 P1 01-JAN-2018 17-SEP-2018 01-JAN-2018
3 P4 400 200 P2 01-JAN-2018 31-DEC-2020 01-JAN-2018
3 P4 400 200 P2 01-JAN-2018 31-DEC-2020 01-JAN-2018
3 P4 400 200 P2 01-JAN-2018 31-DEC-2020 01-JAN-2018
4 P5 500 400 P4 01-JAN-2018 31-DEC-2020 01-JAN-2018
4 P5 500 400 P4 01-JAN-2018 31-DEC-2020 01-JAN-2018
4 P5 500 400 P4 01-JAN-2018 31-DEC-2020 01-JAN-2018
5 P1 100 500 P5 18-SEP-2018 31-DEC-2020 01-JAN-2018
5 P1 100 500 P5 18-SEP-2018 31-DEC-2020 01-JAN-2018
5 P1 100 500 P5 18-SEP-2018 31-DEC-2020 01-JAN-2018