Solved

Select statement using Hierarchy

Posted on 2014-03-17
6
609 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 73

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

914 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now