Link to home
Start Free TrialLog in
Avatar of keenmar
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?
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial