Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Select statement using Hierarchy

Posted on 2014-03-17
6
Medium Priority
?
622 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

What we learned in Webroot's webinar on multi-vector protection.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

577 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