This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Dec 13, 2012 3:59 AM by ryansun RSS

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

ryansun Newbie
Currently Being Moderated
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. Re: Start with Connect by: Showing the whole hierarchy even if child parameter
    jeneesh Guru
    Currently Being Moderated
    If Re: Start with Connect By: What if Parent is the Child? doesn't help you you could try UNION also
    select *
    from (
       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 =171 
        connect by prior EMPLOYEE_ID = MANAGER_ID
        order siblings by FIRST_NAME
        )
    union all
     select 1,1,FIRST_NAME,null,EMPLOYEE_ID,FIRST_NAME,null
     from EMPLOYEES
     where EMPLOYEE_ID = 171
     and not exists (select null from employees where manager_id=171);
  • 2. Re: Start with Connect by: Showing the whole hierarchy even if child parameter
    ryansun Newbie
    Currently Being Moderated
    Hi Jeenesh,

    This query will only return one record for the employee(child). I want it to return the whole hierarchy including the 171 manager record and his managers manager if any(Basically the ultimate parent).

    So in essence If 171 has manager 170 and 170 has manager 169

    The the query output should be what it would have been if I ran "start with manager_id = 169" although we are running it for "start with manager_id = 171 or employee_id = 171"

    So basically the out put in this example should be the same for

    "start with manager_id = 169" and if we run it for "start with manager_id = 171 or employee_id = 171"

    Basically, I want to show the whole hierarchy for that employee selected irrespective if he is a manager or not.

    Does that make sense?

    Thanks,
    Ryan

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

    Edited by: ryansun on Dec 12, 2012 1:29 AM
  • 3. Re: Start with Connect by: Showing the whole hierarchy even if child parameter
    jeneesh Guru
    Currently Being Moderated
    The highest manager will be always the one with MANGER_ID as null, right?

    So what ever be your input, output will be same, right?
  • 4. Re: Start with Connect by: Showing the whole hierarchy even if child parameter
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    maybe you should post your expected output with different cases.

    See if the one below is matching your requirements:
    WITH entire_tree AS
    (
        SELECT *
          FROM employees
         START WITH manager_id = 171
       CONNECT BY PRIOR employee_id = manager_id
       UNION
        SELECT *
          FROM employees
         START WITH employee_id = 171
       CONNECT BY employee_id = PRIOR manager_id
    )
     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 entire_tree
      START WITH manager_id IS NULL
    CONNECT BY PRIOR employee_id = manager_id
      ORDER SIBLINGS BY first_name;
    Regards.
    Al
  • 5. Re: Start with Connect by: Showing the whole hierarchy even if child parameter
    chris227 Guru
    Currently Being Moderated
    As you didnt give us the expected output, i dont get really deep into it. May be
    select 
     empno
    ,ename
    ,mgr
    ,level
    from emp
    start with (empno = 7782 or mgr = 7782)
    connect by nocycle 
     empno = prior mgr
    order by level desc
    
    EMPNO ENAME MGR LEVEL 
    7839 KING -  3 
    7782 clark 7839 2 
    7839 KING -  2 
    7782 clark 7839 1 
    7934 miller 7782 1 
    
    Ok distinct
    
    select distinct
     empno
    ,ename
    ,mgr
    from emp
    start with (empno = 7782 or mgr = 7782)
    connect by nocycle 
     empno = prior mgr
    
    EMPNO ENAME MGR 
    7839 KING -  
    7782 clark 7839 
    7934 miller 7782 
    Edited by: chris227 on 12.12.2012 01:58
  • 6. Re: Start with Connect by: Showing the whole hierarchy even if child parameter
    ryansun Newbie
    Currently Being Moderated
    Thanks Alberto. This is what I was looking for!
  • 7. Re: Start with Connect by: Showing the whole hierarchy even if child parameter
    chris227 Guru
    Currently Being Moderated
    Alberto is very fast (faster then me second time now in a row), but perhaps sometimes it is worth to consider the other answers too.
    Even if i have to correct it ;-)

    Edited by: chris227 on 12.12.2012 01:58
  • 8. Re: Start with Connect by: Showing the whole hierarchy even if child parameter
    AlbertoFaenza Expert
    Currently Being Moderated
    chris227 wrote:
    Alberto is very fast (faster then me second time now in a row), but perhaps sometimes it is worth to consider the other answers too.
    :D I did not know it was a race!! Sorry, nothing personal.

    It was just by chance, sometimes it happens to me too to be late in answering.
    At the end what it is good to know is that we were on the same way. And I keep on learning from other posts as well.

    Regards.
    Al

    Edited by: Alberto Faenza on Dec 12, 2012 10:58 AM
  • 9. Re: Start with Connect by: Showing the whole hierarchy even if child parameter
    chris227 Guru
    Currently Being Moderated
    Alberto Faenza wrote:
    chris227 wrote:
    Alberto is very fast (faster then me second time now in a row), but perhaps sometimes it is worth to consider the other answers too.
    :D I did not know it was a race!! Sorry, nothing personal.
    Not at all. Nothing personal for sure.
    But look at the encoding problem in the other thread.
    Ok, replace might be the first idea to come in mind.

    But i
    1, saved the file to desktop
    2. searched for the poblematic sign.
    3. Tried out the encoding
    4. Tested it in IE and FF

    So this took some time ...

    Regards
  • 10. Re: Start with Connect by: Showing the whole hierarchy even if child parameter
    ryansun Newbie
    Currently Being Moderated
    Hi Chris :) Yes, I did check on your query also. :) Thanks!
  • 11. Re: Start with Connect by: Showing the whole hierarchy even if child parameter
    AlbertoFaenza Expert
    Currently Being Moderated
    chris227 wrote:
    Alberto Faenza wrote:
    chris227 wrote:
    Alberto is very fast (faster then me second time now in a row), but perhaps sometimes it is worth to consider the other answers too.
    :D I did not know it was a race!! Sorry, nothing personal.
    Not at all. Nothing personal for sure.
    But look at the encoding problem in the other thread.
    Ok, replace might be the first idea to come in mind.

    But i
    1, saved the file to desktop
    2. searched for the poblematic sign.
    3. Tried out the encoding
    4. Tested it in IE and FF

    So this took some time ...

    Regards
    In fact I that thread I added a post saying that you were right.
    You actually did a good job, nothing to say.

    Regards.
    Al
  • 12. Re: Start with Connect by: Showing the whole hierarchy even if child parameter
    ryansun Newbie
    Currently Being Moderated
    Hi Jeenesh,

    Just wanted the whole hierarchy for the specific child entered and not all the records. Or else yes, the output would always be the same :). Alberto and Chris's output does just that.

    Thanks for taking the time out

    thanks,
    Ryan
  • 14. Re: Start with Connect by: Showing the whole hierarchy even if child parameter
    ryansun Newbie
    Currently Being Moderated
    Hi Alberto,

    I was using this query and the output is correct. Except for one thing, the nature of our data is such that the top most manager in this case has "Manager_Id" as null so we can use "start with Manager is null"

    But in our case the manager id for the top most manager is the same as his employee id.

    So If I modify the query, it then does not show the "top most managers record"
    WITH entire_tree AS
    (
        SELECT *
          FROM employees
         START WITH manager_id = 171
       CONNECT BY PRIOR employee_id = manager_id
       UNION
        SELECT *
          FROM employees
         START WITH employee_id = 171
       CONNECT BY employee_id = PRIOR manager_id
    )
     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 entire_tree
      START WITH manager_id = 100
    CONNECT BY PRIOR employee_id = manager_id
      ORDER SIBLINGS BY first_name;
    the only change I made is from
    Start with manager_id is null
    
    to 
    
    start with manager_id = 100
    Basically, in this case the manager_id of the top most manager is the same as his employee id. So how can we have that condition incorporated instead of checking for null.

    Thanks,
    Ryan
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points