Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2012
    Posts
    18

    Unanswered: Connect by to Get Parent and Supervisor Details in Oracle

    Hi,
    I have a table like below:

    EMP_ID MGR_ID EMP_NAME EMP_ROLE EMP_HIERARCHY
    10 9 John Developer 7,8,9,10
    9 8 Charlie Manager 7,8,9
    8 7 Bruce GL 7,8
    7 King CEO 7
    20 18 Jack Developer 7,16,17,18,20
    18 17 Adam Teamlead 7,16,17,18
    17 16 Erik Manager 7,16,17
    16 7 David GL 7,16

    We can see that the for each employee, there is a corresponding Manager tagged except for CEO of the company.

    Now, I want the output to be like below

    EMP_ID EMP_NAME EMP_ROLE MGR_ID MGR_NAME MGR_ROLE SUPERVISOR_ID SUPERVISOR_NAME SUPERVISOR_ROLE

    Here, MGR_ID is nothing but the MGR_ID from the same row and Supervisor_id is nothing but the MGR_ID of MGR_ID

    Ex:- For 10 EMP_ID, the MGR_ID = 9 and Supervisor_id = Mgrid of 9 i.e., 8
    For 8, the Mgr_id = 7 and Supervisor_id = Mgrid of 7 which is none. etc.,

    The same logic applies for Mgr_name, Mgr_Role , Supervisor_name and Supervisor_role too.

    I could not format the data in a tabular format due to some formatting issues.

    Could someone please help to develop the logic using connect by function in Oracle?

    Asha

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2012
    Posts
    18
    Hi,

    I tried to develop the logic based on the given url..

    But the problem is, most of the examples for Connect by explains from top level to down level i.e., from CEO to Developer.

    But, for my logic, i want the bottom up approach i.e., from Developer to CEO or to be precise, from any employee, i should be able to fetch details for top 2 parent levels.

    Could you help me further.

    Asha

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by ashachandrika View Post
    But the problem is, most of the examples for Connect by explains from top level to down level i.e., from CEO to Developer.

    But, for my logic, i want the bottom up approach i.e., from Developer to CEO or to be precise, from any employee, i should be able to fetch details for top 2 parent levels.
    Hi,

    it does not seem to be a problem to me - if all employees have the existing manager, you will get all of them when starting from CEO (empty = NULL MGR_ID).

    You could extract the higher level details e.g. by parsing of the result of SYS_CONNECT_BY_PATH function.

    But, as you want only two levels up, what is wrong with using two outer joins instead?

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >But, as you want only two levels up, what is wrong with using two outer joins instead?
    Not part of the homework assignment requirements
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •