Solved

Select statement using Hierarchy

Posted on 2014-03-17
6
612 Views
Last Modified: 2014-03-18
I want to write a query(I think I can use SYS_CONNECT_BY_PATH) to get the managers of a person that is being queried. I would pass in the employee_id and they have a field "at_reports_to" that has their managers employee_id, then I would like to keep going and get their managers manager and so on....My co-worker said he tried this and didnt end up finishing this and he sent me this and it doesnt work so any help would be appreciated...

 SELECT
        SYS_CONNECT_BY_PATH(at_first_name || ' ' || at_last_name, '/')
        FROM MYTABLE WHERE
        CONNECT_BY_ROOT at_user =upper(12345)  
        CONNECT BY PRIOR at_user = at_reports_to;

The field at_reports_to holds the employee id of the person’s manager
0
Comment
Question by:jknj72
  • 3
  • 3
6 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39935309
SELECT manager_path
  FROM (    SELECT SYS_CONNECT_BY_PATH(at_first_name || ' ' || at_last_name, '/') manager_path,
                   CONNECT_BY_ISLEAF isleaf
              FROM mytable
        START WITH at_user = 12345
        CONNECT BY at_user = PRIOR at_reports_to)
 WHERE isleaf = 1
0
 

Author Comment

by:jknj72
ID: 39936795
ok this didnt work out like I needed so I wanna show you maybe the data and just so you know, I dont need to use the above functions if I dont need to. I have a view Im reading from and everyone has an at_employee_number and their is another field, at_reports_to, which has their managers at_employee_number(not everyone has a value in this field).
What I need is to bring back the hierarchy. If their manager has a value in their at_reports_to field then bring that back and keep kinda climbing up the hierarchy. Initially I thought I could do a recursive query and try and bring back the data like that but whatever works.....
Below is a picture of the data. If I query at_employee_number = 395200 I would wanna get back......

manager_path
ROSEANNE/KAREN/MICHAEL
Hierarchy.jpg
0
 

Author Comment

by:jknj72
ID: 39936796
FYI, this could be returned as rows or a delimited string or whatever
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39936813
Same query, just use the real tablename and column names


SELECT manager_path
  FROM (    SELECT SYS_CONNECT_BY_PATH(at_first_name, '/') manager_path, CONNECT_BY_ISLEAF isleaf
              FROM vw_omni_users
        START WITH at_employee_number = 395200
        CONNECT BY at_employee_number = PRIOR at_reports_to)
 WHERE isleaf = 1
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39936816
and if you don't want the leading "/" just substr the results

SELECT manager_path
  FROM (    SELECT SUBSTR(SYS_CONNECT_BY_PATH(at_first_name, '/'), 2) manager_path,
                   CONNECT_BY_ISLEAF isleaf
              FROM vw_omni_users
        START WITH at_employee_number = 395200
        CONNECT BY at_employee_number = PRIOR at_reports_to)
 WHERE isleaf = 1
0
 

Author Closing Comment

by:jknj72
ID: 39936841
like a charm...thanks sdtuber
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question