hierachical query to return final row

Posted by jeff on Stack Overflow See other posts from Stack Overflow or by jeff
Published on 2011-01-03T20:39:38Z Indexed on 2011/01/03 21:54 UTC
Read the original article Hit count: 399

Filed under:
|
|
|

I have a hierarchical query that doesn't return an expected row (employee badge = 444).

TABLE: hr_data

badge fname supervisor_badge
111   Jeff  222
222   Joe   333
333   John  444
444   Tom   444

SQL:

SELECT CONNECT_BY_ISCYCLE As IC, 
       badge, 
       fname, 
       supervisor_badge
  FROM hr_data 
START WITH badge = '111' 
CONNECT BY NOCYCLE badge = PRIOR supervisor_badge 

What is Returned:

IC badge fname supervisor_badge
0 111   Jeff  222
0 222   Joe   333
1 333   John  444

What is Expected:

IC badge fname supervisor_badge
0 111   Jeff  222
0 222   Joe   333
**0** 333   John  444
**1** 444   Tom   444

How can I get this query to return the employee Tom and then stop?

© Stack Overflow or respective owner

Related posts about sql

Related posts about Oracle