Hierarchical Queries "START WITH" with where clause behavior

Question

I came across a query during work and could not figure out how exactly it works. What the query does is look for all the parents to a person that are its parent today.

Now the trick here is that each parent child relationship has a duration for which it is valid.

Take this data set as a reference:

GrandParent is parent of Father from 01-01-2012 to 02-02-2015

Father is parent of Child from 01-01-2012 to 02-02-2011

Child is just the lowest level person

NewFather is parent of Child from 01-01-2012 to 02-02-2014

now the list of parents valid today for Child should consist only of NewFather

to get the list, previously we used this SQL:

SELECT connect_by_root per_id2 AS per_id2,
       per_id1,
       LEVEL                   AS per_level,
       n.entity_name
FROM   ci_per_per pp,
       ci_per_name N
WHERE  N.per_id = per_id1
       AND start_dt <= SYSDATE
       AND ( end_dt IS NULL
              OR end_dt >= SYSDATE )
START WITH per_id2 = :personID
CONNECT BY NOCYCLE PRIOR per_id1 = per_id2;

where personID is a binded variable

this query did not work because the where clause behavior is such that it first gets all the records and then checks for the non-join conditions (checks for start date and end date). This results in it giving list of parents as NewFather, GrandParent which is completely WRONG!

Thus, the query was changed to the Following:

SELECT connect_by_root per_id2 AS per_id2,
       per_id1,
       LEVEL                   AS per_level,
       n.entity_name
FROM   ci_per_per pp,
       ci_per_name N
WHERE  N.per_id = per_id1
       AND start_dt <= SYSDATE
       AND ( end_dt IS NULL
              OR end_dt >= SYSDATE )
START WITH per_id2 = (SELECT per_id
                      FROM   ci_acct_per
                      WHERE  per_id = :personID
                             AND pp.start_dt <= SYSDATE
                             AND ( pp.end_dt IS NULL
                                    OR pp.end_dt >= SYSDATE ))
CONNECT BY NOCYCLE PRIOR per_id1 = per_id2;

Now what I don't understand is:

how can a where condition in the start with clause affect the behavior of the query in such a manner?

Another thing that I dislike about this query is that it uses a completely unrelated table named ci_acct_per which simply has a column of per_id in it for each person in ci_per_per.

Can we do better? Is a cleaner approach available for the fixing the original query?

UPDATE

This query works only if traveling higher in the hierarchy and not if we are looking for children. However, this query never looks for children and is not supposed to.

Answer 1

I'm not sure that I understand you right, but why not:

SELECT connect_by_root per_id2 AS per_id2,
       pp.per_id1,
       LEVEL                   AS per_level,
       n.entity_name
FROM   (select * 
        from ci_per_per
       where start_dt <= SYSDATE
       AND ( end_dt IS NULL
              OR end_dt >= SYSDATE )) pp join
       ci_per_name N on N.per_id = pp.per_id1         
START WITH per_id2 = 1
CONNECT BY NOCYCLE PRIOR pp.per_id1 = pp.per_id2;

Here is a sqlfiddle demo

---

Update Thanks to @user1395 example:

It's hard to explain how come the strange query works because it doesn't...

What really happens is that the START WITH clause uses per_id2 which is the "father of" column, so if there are more than one (one isn't relevant to sysdate) you still need not to start with it.
In other words it doesn't start from "child" but from "child" fathers - "father" and "newfather".

So, either use @user1395 suggestion of having date logic in both the connect by clause to stop when a father isn't relevant, and start with clause to make only the relevant father available, or remove all unrelevant fathers on the first place (as in my former suggestion) or "start with" the "child" and not its fathers:

select * from (
SELECT connect_by_root per_id1 AS per_id2,
       per_id1,
       LEVEL                   AS per_level,
       n.entity_name
FROM   ci_per_per pp,
       ci_per_name N
WHERE  N.per_id = per_id1       
START WITH per_id1 = 1
CONNECT BY NOCYCLE PRIOR per_id1 = per_id2 AND start_dt <= SYSDATE
       AND ( end_dt IS NULL
              OR end_dt >= SYSDATE ))
where per_id1 <> per_id2;

Another sqlfiddle demo

Answer 2

You could use date logic inside connect by clause.

SELECT connect_by_root per_id2 AS per_id2,
       per_id1,
       LEVEL                   AS per_level,
       n.entity_name
FROM   ci_per_per pp,
       ci_per_name N
WHERE  N.per_id = per_id1
START WITH per_id2 = :personID AND 
                         SYSDATE BETWEEN start_dt AND NVL(end_dt,SYSDATE)
CONNECT BY NOCYCLE PRIOR per_id1 = per_id2 AND 
                         SYSDATE BETWEEN start_dt AND NVL(end_dt,SYSDATE);

This will stop climbing up when there are no valid parents for the current date.