StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I'm not sure that I understand you right, but why not: </p> <pre><code>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; </code></pre> <p><a href="http://www.sqlfiddle.com/#!4/1c092/1" rel="nofollow">Here is a sqlfiddle demo</a></p> <hr> <p><strong>Update</strong> Thanks to @user1395 <a href="http://www.sqlfiddle.com/#!4/d3973/1" rel="nofollow">example</a>:</p> <p>It's hard to explain how come the strange query works because it doesn't... </p> <p>What really happens is that the <code>START WITH</code> 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 <strong>not</strong> to start with it.<br> In other words it doesn't start from "child" but from "child" fathers - "father" and "newfather". </p> <p>So, either use @user1395 suggestion of having date logic in both the <code>connect by</code> clause to stop when a father isn't relevant, and <code>start with</code> 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: </p> <pre><code>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; </code></pre> <p><a href="http://www.sqlfiddle.com/#!4/d3973/17" rel="nofollow">Another sqlfiddle demo</a></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