?
Solved

Select statement using Hierarchy

Posted on 2014-03-17
6
Medium Priority
?
621 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

649 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