?
Solved

Querying a table and returning a heirarchy

Posted on 2007-10-10
10
Medium Priority
?
185 Views
Last Modified: 2013-12-07
Business Goal: Management has asked us to return the listing of everyone name who is currently reports to them and everyone under them and so and so forth.

Currently, we have an Oracle database containing a table named as PA_STUDENT. This PA_STUDENT table contains all the employees who work in our organization in column A and the name of their supervisor in column B.

What I need to get from PA_STUDENT is for example:

If I query the name of the Director, then I should return all the directors subordinates and the subordinates' subordinates. If the Director had Joe, Michael, and Dave working under him as group leaders, and Joe, Michael, and Dave have 6 people working under of them, and those 6 people had more people working under each of them and so and so forth, I would like to see all those names come up.

I remember that when I used to take C/C++ classes, we learned the concept of "binary search" trees where 1 parent has 2 child nodes. In this case, we have parents who have multiple children (the supervisors direct reports) and those children also have multiple children.

I hope this makes sense. How can I get this accomplished assuming that I have PA_STUDENT as the table using SQL to grab this information? Please advise.
0
Comment
Question by:piyushdabomb
  • 4
  • 3
  • 2
9 Comments
 
LVL 18

Expert Comment

by:sventhan
ID: 20053586
0
 
LVL 18

Expert Comment

by:sventhan
ID: 20053609
another good one

http://www.experts-exchange.com/Database/Oracle/Q_21536595.html?sfQueryTermInfo=1+%22connect+prior%22

You could find so many reulsts if you do a little search on EE itself.

Please let me know if you are looking for anythig specific
0
 

Author Comment

by:piyushdabomb
ID: 20053612
From the link above, it looks like this is PL/SQL; is this true? Are there any alternatives to connecting without using PL/SQL?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:sventhan
ID: 20053631
You could do it in SQL using Connect by prior clause.

Not neccessary to use PL/SQL
0
 
LVL 18

Expert Comment

by:sventhan
ID: 20053642
in your case the query would be

select lpad(' ',2*(level-1)) || to_char(column_a)  
  from pa_student
  start with parent is null
  connect by prior column_a = column_b
0
 

Author Comment

by:piyushdabomb
ID: 20053672
Sventhan,

Thank you for your advise so far. May I request you to provide an explanation to your code?

If the supervisor is in column B and the user is in column A, this is the pseudocode to eventually satisfy the SQL command:

Note: Instead of working with names, I need to work with a Universal ID. Each name has a unique identifier which is the Universal ID (UPI #)

Look for the UPI # in column B and extract all the UPI #'s in column B
Then take each extracted UPI # and then go back to column B and look for the corresponding rows.

Example:

DirectorA - UPI #: 1111
SubordinateA (child of Subordinate DirectorA) - UPI# 2_1111
SubordinateB (child of Subordinate DirectorA) - UPI# 2_2222
Subordinate AA (child of SubordinateA) UPI #: 3_1111
Subordinate AB (child of SubordinateA) UPI# 3_2222
Subordinate BA (child or SubordinateB) UPI#: 1233_1
Subordinate BB (child or SubordinateB) UPI#: 1233_2
Subordinate BC (child or SubordinateB) UPI#: 1233_3
Subordinate BAA (child or SubordinateBA) UPI#: 1233_1_1111
etc...

The result should get me all the values in a tree form. In SQL, since they come out as a table, that is fine.

Note: I don't know how many levels deep this can go but I don't think it should matter.


0
 
LVL 27

Accepted Solution

by:
sujith80 earned 1500 total points
ID: 20054500
You can use hierarchical queries in Oracle. See the example below.
This query traverses from an employee, down all the way to the subordinates. The column level indicates the depth of the tree.

SQL> select empno, ename, mgr  from emp;

     EMPNO ENAME             MGR
---------- ---------- ----------
      7369 SMITH            7902
      7499 ALLEN            7698
      7521 WARD             7698
      7566 JONES            7839
      7654 MARTIN           7698
      7698 BLAKE            7839
      7782 CLARK            7839
      7788 SCOTT            7566
      7839 KING
      7844 TURNER           7698
      7876 ADAMS            7788
      7900 JAMES            7698
      7902 FORD             7566
      7934 MILLER           7782

14 rows selected.

SQL> select empno, mgr, ename, prior ename, level
   from emp
   start with empno = 7839
   connect by prior empno = mgr
   /

  EMPNO        MGR ENAME      PRIORENAME      LEVEL
---------- ---------- ---------- ---------- ----------
      7839                 KING                           1
      7566       7839 JONES      KING                2
      7788       7566 SCOTT      JONES               3
      7876       7788 ADAMS      SCOTT               4
      7902       7566 FORD       JONES               3
      7369       7902 SMITH      FORD                4
      7698       7839 BLAKE      KING                2
      7499       7698 ALLEN      BLAKE               3
      7521       7698 WARD       BLAKE               3
      7654       7698 MARTIN     BLAKE               3
      7844       7698 TURNER     BLAKE               3
      7900       7698 JAMES      BLAKE               3
      7782       7839 CLARK      KING                2
      7934       7782 MILLER     CLARK               3

14 rows selected.

SQL>
0
 

Author Comment

by:piyushdabomb
ID: 20083023
Thanks for everyones suggestion however, there are problems with this.

Although the connect by prior seems to work using Oracle Toad, I need to implement the exact same code snippets shown above into Excel for Visual Basic for applications. Using MS Query, connect by prior is not an applicable query. How else can I make this work? Can I somehow include the connect by prior into my VBA code? Please advise.
0
 
LVL 27

Expert Comment

by:sujith80
ID: 20114256
"Connect by proior" is an oracle syntax. As long as you can run an ORACLE query from anywhere, it should work. I am not sure about the limitations you have mentioned.
If your interface has syntax limitations, have this query as a stored procedure in your database and invoke the same.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

864 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