0

I'm trying to reproduce the first simple example from this tutorial.

However, I don't understand why I get so many rows back. I would expect that 1 reports to 2 only at level 1 and not at level 2 as well.

SQL> select employee_id, reports_to from employees;

EMPLOYEE_ID REPORTS_TO
----------- ----------
      1      2
      2
      3      2
      4      2
      5      2
      6      5
      7      5
      8      2
      9      5

9 rows selected.

SQL> select employee_id, reports_to, LEVEL from employees connect by prior employee_id = reports_to;

EMPLOYEE_ID REPORTS_TO      LEVEL
----------- ---------- ----------
      1      2      1
      8      2      1
      5      2      1
      6      5      2
      9      5      2
      7      5      2
      4      2      1
      3      2      1
      6      5      1
      9      5      1
      7      5      1

EMPLOYEE_ID REPORTS_TO      LEVEL
----------- ---------- ----------
      2         1
      1      2      2
      8      2      2
      5      2      2
      6      5      3
      9      5      3
      7      5      3
      4      2      2
      3      2      2

20 rows selected.
1

You're getting many rows because without START WITH it outputs all possible hierarchies . You see 1 reports 2 on level 1 (1->2), and another one on level 2 (1->2->NULL). Adding SYS_CONNECT_BY_PATH(reports_to, '/') "Path" to select will help in visualizing that.
Let's connect just 2 rows for illustration:

with test1 as 
(
select 1 as employee_id, 2 as reports_to from dual
union all 
select 2 as employee_id, NULL as reports_to from dual


)
select employee_id,reports_to ,level ,SYS_CONNECT_BY_PATH(NVL(to_char(reports_to),'NULL'), '/') "Path"

from test1 
--start with employee_id=2
connect by prior employee_id  =reports_to;

will output :

Employee    ReportTo   Level Path
1              2        1     /2  
2              <null>   1     /NULL  
1              2        2     /NULL/2  

If we uncomment "start with" , the result will be

2   <null>  1   /NULL
1      2    2   /NULL/2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.