StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHierarchical Queries "START WITH" with where clause behavior
text
Body
copied!<p>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.</p> <p>Now the trick here is that each parent child relationship has a duration for which it is valid.</p> <p>Take this data set as a reference:</p> <blockquote> <p>GrandParent is parent of Father from 01-01-2012 to 02-02-2015</p> <p>Father is parent of Child from 01-01-2012 to 02-02-2011</p> <p>Child is just the lowest level person</p> <p>NewFather is parent of Child from 01-01-2012 to 02-02-2014</p> </blockquote> <p>now the list of parents valid today for Child should consist only of <code>NewFather</code></p> <p>to get the list, previously we used this SQL:</p> <pre><code>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; </code></pre> <p>where <code>personID</code> is a binded variable</p> <p>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 <code>NewFather, GrandParent</code> which is completely WRONG!</p> <p>Thus, the query was changed to the Following:</p> <pre><code>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; </code></pre> <p>Now what I don't understand is:</p> <blockquote> <p>how can a where condition in the start with clause affect the behavior of the query in such a manner?</p> </blockquote> <p>Another thing that I dislike about this query is that it uses a completely unrelated table named <code>ci_acct_per</code> which simply has a column of <code>per_id</code> in it for each person in <code>ci_per_per</code>.</p> <blockquote> <p>Can we do better? Is a cleaner approach available for the fixing the original query?</p> </blockquote> <p><strong>UPDATE</strong></p> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload