Hierarchical Query CONNECT BY Oracle

Questions

I am trying to implement the Connect By query in oracle. Trying to understand how it works. So I have a simple table which looks likes:

empno ename mno
1      KS    null
2      AB     2
3      BC     1
4      TR     3
5      QE     2
6      PL     3
7      LK     6

The Query

SELECT empno, ename, mno
   FROM test
START WITH ename =  LK 
   CONNECT BY PRIOR empno = mno;

So when the name is LK I should get the following parent/child rows LK>PL>BC>KS. http://sqlfiddle.com/#!4/508f7/5 http://sqlfiddle.com/#!4/508f7/5

Answers

No, you should not.

LK s parent is PL. PL s parent is BC. BC s parent is KS. KS has no parent. When you re trying to start tree from LK, you get all it s children - none, because there are no records with mno = 7.

You muddled parent-child order in your query. If you wish to unwind the three from leaf to root, you should ask

SELECT empno, ename, mno
   FROM test
START WITH ename =  LK 
   CONNECT BY empno = PRIOR mno;

If you wish to see the tree into the natural order, you should to ask

SELECT empno, ename, mno
   FROM test
START WITH mno is null
   CONNECT BY PRIOR empno = mno;

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/28501879/hierarchical-query-connect-by-oracle

Related