title: Oracle - hierarchical bottom up query with time constraint
body: <p>I have a table that stores the relationships between organizational units.
  These relations can change in the time, so it's perfectly normal to have an object thet refers to a parent yesterday, and to another parent today.
  A constraint in DB ensures that an object can refers only to a parent in a given time (1:1 relation)</p>
  
  <p>Now I want a query that returns the path from the leaf to the root, the only valid path in the desired period. </p>
  
  <p>I am able to make the query run if I have only one relation, but if I insert two consecutive relation my query fails, returning not only my path, but also the other organization objects not belonging to my path</p>
  
  <p>These are the relations</p>
  
  <pre><code>-- old situation graph
  --           1
  --       /   |  \
  --      11   12  13
  --      |    |
  --     111  121
  --           |
  --         1111
  --
  -- current situation graph
  --           1
  --       /   |  \
  --      11   12  13
  --      |    |
  --     111  121
  --      |
  --    1111
  </code></pre>
  
  <p>With only one relation I get this result for today and yesterday (OK)</p>
  
  <pre><code>'CURRENTSITUATION'  KY_UNI_ORG_INF  KY_UNI_ORG_SUP
  Current situation   1111            111
  Current situation   111              11
  Current situation   11                1
  Current situation   1                 0
  
  'OLDSITUATION'  KY_UNI_ORG_INF  KY_UNI_ORG_SUP
  old situation   111             11
  old situation   11               1
  old situation   1                0
  </code></pre>
  
  <p>but with two relations (uncommenting the DML sentence) I got the WRONG situation</p>
  
  <pre><code>'CURRENTSITUATION'  KY_UNI_ORG_INF  KY_UNI_ORG_SUP
  Current situation   1111            111
  Current situation   111              11
  Current situation   121              12  -- Not in my path
  Current situation   12                1  -- Not in my path
  Current situation   11                1
  Current situation   1                 0
  Current situation   1                 0
  
  'OLDSITUATION'  KY_UNI_ORG_INF  KY_UNI_ORG_SUP
  old situation   1111            121
  old situation   121             12
  old situation   111             11  -- Not in my path
  old situation   11               1  -- Not in my path
  old situation   12               1
  old situation   1                0
  old situation   1                0
  </code></pre>
  
  <p>Following some DDL</p>
  
  <pre><code>CREATE TABLE BORG_APP_UNI_ORG 
  (
    KEY_TAB NUMBER(10, 0) NOT NULL 
  , KY_UNI_ORG_SUP NUMBER(10, 0) NOT NULL 
  , KY_UNI_ORG_INF NUMBER(10, 0) NOT NULL 
  , DATA_INIZIO DATE NOT NULL 
  , DATA_FINE DATE NOT NULL
  ) 
  </code></pre>
  
  <p>DML    </p>
  
  <pre><code>INSERT INTO BORG_APP_UNI_ORG VALUES (    1,  0,    1, trunc(sysdate-30), trunc(sysdate+30) );
  INSERT INTO BORG_APP_UNI_ORG VALUES (   11,  1,   11, trunc(sysdate-30), trunc(sysdate+30) );
  INSERT INTO BORG_APP_UNI_ORG VALUES (   12,  1,   12, trunc(sysdate-30), trunc(sysdate+30) );
  INSERT INTO BORG_APP_UNI_ORG VALUES (   13,  1,   13, trunc(sysdate-30), trunc(sysdate+30) );
  INSERT INTO BORG_APP_UNI_ORG VALUES (  111, 11,  111, trunc(sysdate-30), trunc(sysdate+30) );
  INSERT INTO BORG_APP_UNI_ORG VALUES (  121, 12,  121, trunc(sysdate-30), trunc(sysdate+30) );
  -- old relat                                                
  -- INSERT INTO BORG_APP_UNI_ORG VALUES ( 1111,121, 1111, trunc(sysdate-30), trunc(sysdate-1) );
  -- new relat                                                
  INSERT INTO BORG_APP_UNI_ORG VALUES ( 1111, 111, 1111, trunc(sysdate),    trunc(sysdate+30) );
  </code></pre>
  
  <p>and the query    </p>
  
  <pre><code>-- Current
  select 'Current situation', r.ky_UNI_ORG_INF, r.ky_UNI_ORG_SUP
  from 
      BORG_APP_UNI_ORG r
  WHERE 
      trunc(sysdate) BETWEEN r.data_inizio and r.data_fine
  CONNECT BY ky_uni_org_inf  = PRIOR ky_uni_org_sup
  START WITH ky_uni_org_inf = 1111
  order by level
      ;
  
  -- Old
  select 'old situation',r.ky_UNI_ORG_INF, r.ky_UNI_ORG_SUP
  from 
      BORG_APP_UNI_ORG r
  WHERE 
      trunc(sysdate-1) BETWEEN r.data_inizio and r.data_fine
  CONNECT BY ky_uni_org_inf  = PRIOR ky_uni_org_sup
  START WITH ky_uni_org_inf = 1111
  order by level
  ;    
  </code></pre>