keenmar
asked on
Which is the equivalent of CONNECT BY PRIOR (from ORACLE) in SQL Server
The following hierarchical query uses the CONNECT BY clause to define the relationship between employees and managers:
(The following code is in ORACLE)
SELECT employee_id, last_name, manager_id, Level
FROM employees
CONNECT BY PRIOR employee_id = manager_id;
EMPLOYEE_ID LAST_NAME MANAGER_ID LEVEL
----------- ------------------------- ---------- ---------- -------------
101 Kochhar 100 1
108 Greenberg 101 2
109 Faviet 108 3
110 Chen 108 3
111 Sciarra 108 3
112 Urman 108 3
113 Popp 108 3
I need to know what is the equivalent of CONNECT BY PRIOR in SQL Server, or how can I cross a hierarchy in SQL Server?
(The following code is in ORACLE)
SELECT employee_id, last_name, manager_id, Level
FROM employees
CONNECT BY PRIOR employee_id = manager_id;
EMPLOYEE_ID LAST_NAME MANAGER_ID LEVEL
----------- ------------------------- ---------- ---------- -------------
101 Kochhar 100 1
108 Greenberg 101 2
109 Faviet 108 3
110 Chen 108 3
111 Sciarra 108 3
112 Urman 108 3
113 Popp 108 3
I need to know what is the equivalent of CONNECT BY PRIOR in SQL Server, or how can I cross a hierarchy in SQL Server?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.