I want to join various nodes of a tree, making sure the the returned root-to-leaf path is temporally valid. The tricky part is that the data source is dated with validity from-to dates.
the links are trivially pointing to the node ids (but not their dates!)
from this I would like to return the valid paths and their validity dates:
invalid paths (e.g.
A-C-E should not be returned, since there is no moment in time in which all the three nodes are valid).
The issue I have with this is that the "overlap" check is not transitive (so A overlaps with B and B overlaps with C does not imply that A overlaps with C). So when writing the
connect by query each level overlaps with the next, but the resulting global path is invalid.
the basic query set up I have is
with src_nodes (id, nvalue, vfrom, vto) as ( select 1, 'A', date '2021-01-01', date '2021-01-31' from dual union all select 1, 'B', date '2021-02-01', date '2021-02-28' from dual union all select 2, 'C', date '2021-01-01', date '2021-02-28' from dual union all select 3, 'D', date '2021-01-01', date '2021-01-31' from dual union all select 3, 'E', date '2021-02-01', date '2021-02-28' from dual ), src_links(link_child, link_parent) as ( select 1, 2 from dual union all select 2, 3 from dual ), full_links as ( select c.* from src_links c union select null, link_child from src_links a where not exists(select null from src_links b where b.link_parent = a.link_child) ), nodes_and_links as ( select * from full_links a join src_nodes n on n.id = a.link_parent) select * from nodes_and_links nl start with nl.link_child is null connect by prior nl.link_parent = nl.link_child and greatest(prior nl.vfrom, nl.vfrom) < least(prior nl.vto, nl.vto)
but I am not sure how to best move forward.