1 2 Previous Next 18 Replies Latest reply: Dec 13, 2012 5:59 AM by Ryansun-Oracle RSS

    Start with Connect by: Showing the whole hierarchy even if child parameter

    Ryansun-Oracle
      11g

      Hi There,

      In our case the manager_id i= employee_id for the top level manager. So when I run the sql, it eliminates the top level manager and shows the output for the next level onwards.

      For the regular start with connect by option on the employee table the query used is
      select case when connect_by_isleaf = 1 then 0 when level = 1 then 1 else -1 end as status,
             level,
             First_name as title,
             NULL  as icon,
             EMPloyee_id as value,
             First_name as tooltip,
             NULL  as link
      from EMPLOYEES
      start with Manager_id is null
      connect by prior EMPLOYEE_ID = Manager_id
      order siblings by First_name
      now this will show the hierarchy and level starting with managers. Now if we provide the "start with Manager_id = 171" then since 171 is not a manager no rows are returned. So for this we can use something like
      start with manager_id =171 or employee_id = 171
      However, the output would be only the employee record since 171 is the last child.

      The requirement we had was that, Irrespective of the value entered, whether ultimate parent or ultimate child the output should still show the top down hierarchy. starting with that persons ultimate manager.

      So for example if there are two employees, 170 and 171 and 170 is the manager of 171

      If in this query I use Start with manager = 170. It will show me the manager and the child records.

      However, if I use start with manager = 171 or employee_id = 171 then it will only show me only the child record. I want it to show me both the manager and employee records. i.e all the levels

      I hope that make sense!

      Thanks,
      Ryan

      Edited by: ryansun on Dec 12, 2012 1:13 AM

      Edited by: ryansun on Dec 13, 2012 1:59 AM
        1 2 Previous Next