Solved

Select statement using Hierarchy

Posted on 2014-03-17
6
605 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

705 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