"START WITH" hierarchical queries, where sentence behavior

Question

I stumbled upon a query while working and was unable to figure out exactly how it works. What the request does is search for all parents for the person who is his parent today.

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

Take this dataset as a link:

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

The father is the parent of the child from 01-01-2012 to 02-02-2011

The child is the lowest level person

NewFather is a parent of a child from 01-01-2012 to 02-02-2014

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

to get the list, we used this SQL earlier:

 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 related variable

this request did not work, because the behavior of the where clause is such that it first receives all the records, and then checks the conditions for the absence of a connection (checks the start date and end date). This causes it to list parents as NewFather, GrandParent , which is completely WRONG!

Thus, the request 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 I dont understand:

how does the where clause at the beginning of the sentence affect the behavior of the request in this way?

Another thing that I don't like about this query is that it uses a completely unrelated table called ci_acct_per , which simply has a per_id column in it for each person in ci_per_per .

Can we do better? Is a cleaner approach available to correct the original request?

UPDATE

This query only works if you are traveling higher in the hierarchy, and not if we are looking for children. However, this query never searches for children and should not.

Answer 1

I'm not sure I understand you correctly, 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 the sqlfiddle daemon

---

Update Thanks @ user1395 example :

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

What really happens is that the START WITH clause uses per_id2, which is the father column, so if there are several of them (one does not apply to sysdate), you still do n't need to start. In other words, this does not begin with the “child”, but with the “paternal” fathers - the “father” and the “new father”.

Thus, either use the @ user1395 clause on the presence of date logic in the connect by clause to stop when the father is irrelevant, and START WITH to make available only the corresponding father or remove all unrelated fathers first place (as in in my previous sentence) or "start with" "baby" and not his 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 can use the date logic in the connect by section.

 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 increasing if there are no valid parents for the current date.