3 Replies Latest reply on Dec 11, 2012 7:21 AM by Ryansun-Oracle

    Start with Connect By: What if Parent is the Child?

      Version : 11g

      Hi There,

      I had a question regarding Start with Connect by.

      The following is an example from the ORacle help from the Emploees table
      select case when connect_by_isleaf = 1 then 0 when level = 1 then 1 else -1 end as status,
             First_name as title,
             NULL  as icon,
             EMPloyee_id as value,
             First_name as tooltip,
             NULL  as link
      from EMPLOYEES
      start with Manager_id ='171'
      connect by prior EMPLOYEE_ID = Manager_id
      order siblings by First_name
      Now this works fine and shows the levels. In the help the start with is actually
      start with Manager_id is null
      Now if we use a lowest level employee using something like
      start with Manager_id = '171'
      no rows are returned. This is because there are no child records for this employee (he is not a manager).

      However, is it possible to atleast show this one record as the output? Any suggestions?


      Edited by: ryansun on Dec 10, 2012 10:51 PM