Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchy, sort of
Hierarchy, sort of [message #290414] Fri, 28 December 2007 15:07 Go to next message
dukeslater
Messages: 11
Registered: November 2007
Location: Oklahoma USA
Junior Member
I need to produce a file that contains a record for every employee, their department, and the departments for which they have a subordinate employee. So far so good, but I also need records for each department for which their subordinates have subordinates, etc. In my case that goes seven levels deep.

So if the raw data looks like this:

Emp     Supervisor      Dept.
1           1             1000
2           1             2000
3           2             3000
4           2             4000
5           3             5000


Then I need:

Emp      Dept.
1        1000
1        2000
1        3000
1        4000
1        5000
2        2000
2        3000
2        4000
2        4000
3        3000
3        5000
4        4000
5        5000


This might be easy, but I'm stumped. Thanks in advance for your help.
Re: Hierarchy, sort of [message #290416 is a reply to message #290414] Fri, 28 December 2007 15:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just a "connect by" statement.
What did you try so far?

By the way, emp 1 is his own supervisor, strange doesn't it.

Regards
Michel
Re: Hierarchy, sort of [message #290419 is a reply to message #290414] Fri, 28 December 2007 16:10 Go to previous messageGo to next message
dukeslater
Messages: 11
Registered: November 2007
Location: Oklahoma USA
Junior Member
select supervisor, department
from table
start with employee = 1
connect by prior employee = supervisor


I did try this earlier, but am getting the ORA-01436 error, loop in user data.

I'm interpreting this as a supervisor has an employee who is the supervisor's supervisor, or something to that effect. With about 6000 employee records, any tips on how to troubleshoot this?

Thanks.
Re: Hierarchy, sort of [message #290423 is a reply to message #290419] Fri, 28 December 2007 16:38 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Change employee #1's supervisor to NULL
Re: Hierarchy, sort of [message #290426 is a reply to message #290419] Fri, 28 December 2007 16:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

but am getting the ORA-01436 error, loop in user data.

I gave you a clue:
Quote:

emp 1 is his own supervisor, strange doesn't it.

Ross gave you the solution.

Regards
Michel
Re: Hierarchy, sort of [message #290427 is a reply to message #290414] Fri, 28 December 2007 16:51 Go to previous messageGo to next message
dukeslater
Messages: 11
Registered: November 2007
Location: Oklahoma USA
Junior Member
This is running now, but the results are not complete. It is returning one record for each supervisor and each of their direct subordinates' departments, but not any deeper than that.

In other words, if you look at my example it is currently returning a record for employee 1 and departments 1000 and 2000, for which he has direct reports.

I need additional records for those departments for which his subordinates have subordinates, so employee 1 would also have records for departments 3000, 4000, and 5000.

Another way to look at it is that employee 1 - who is the president of the company - would have a record for every department, since all departments roll up to him.

Thanks for your help.



Re: Hierarchy, sort of [message #290429 is a reply to message #290427] Fri, 28 December 2007 17:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case (create table and insert statements)
use SQL*Plus and copy and paste the execution of your statement.

Forgot:
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel

[Updated on: Fri, 28 December 2007 17:04]

Report message to a moderator

Re: Hierarchy, sort of [message #290550 is a reply to message #290429] Sat, 29 December 2007 16:30 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
select employee, connect_by_root department
from table
connect by prior employee = supervisor


Ross Leishman
Re: Hierarchy, sort of [message #290578 is a reply to message #290550] Sun, 30 December 2007 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this a solution?
I don't think it answers the problem.

Regards
Michel
Re: Hierarchy, sort of [message #290589 is a reply to message #290550] Sun, 30 December 2007 03:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select emp, connect_by_root dept
  2  from t
  3  connect by prior emp = mgr
  4  /
       EMP CONNECT_BY_ROOTDEPT
---------- -------------------
         2                2000
         3                2000
         5                2000
         4                2000
         3                3000
         5                3000
         4                4000
         5                5000
         1                1000
         2                1000
         3                1000
         5                1000
         4                1000

13 rows selected.

It is more:
SQL> select connect_by_root emp, dept
  2  from t
  3  connect by prior emp = mgr
  4  /
CONNECT_BY_ROOTEMP       DEPT
------------------ ----------
                 2       2000
                 2       3000
                 2       5000
                 2       4000
                 3       3000
                 3       5000
                 4       4000
                 5       5000
                 1       1000
                 1       2000
                 1       3000
                 1       5000
                 1       4000

13 rows selected.

Regards
Michel
Re: Hierarchy, sort of [message #290760 is a reply to message #290414] Mon, 31 December 2007 09:13 Go to previous messageGo to next message
dukeslater
Messages: 11
Registered: November 2007
Location: Oklahoma USA
Junior Member
Back in the office, and thank you for your replies.

I believe that connect_by_root is only valid in version 10 and above; I'm using 9i.

Any other ideas?

Thanks again.
Re: Hierarchy, sort of [message #290766 is a reply to message #290760] Mon, 31 December 2007 09:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select sys_connect_by_path(emp,'/') emp,
  2         substr(sys_connect_by_path(emp,'/'),
  3                2,
  4                instr(sys_connect_by_path(emp,'/')||'/','/',1,2)-2) emp,
  5         dept
  6  from t
  7  connect by prior emp = mgr
  8  /
EMP        EMP              DEPT
---------- ---------- ----------
/2         2                2000
/2/3       2                3000
/2/3/5     2                5000
/2/4       2                4000
/3         3                3000
/3/5       3                5000
/4         4                4000
/5         5                5000
/1         1                1000
/1/2       1                2000
/1/2/3     1                3000
/1/2/3/5   1                5000
/1/2/4     1                4000

13 rows selected.

Regards
Michel
Re: Hierarchy, sort of [message #290800 is a reply to message #290578] Mon, 31 December 2007 20:02 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Michel Cadot wrote on Sun, 30 December 2007 18:22

Is this a solution?
I don't think it answers the problem.

Regards
Michel


Yeah, just me being lazy and getting the CONNECT_BY_ROOT in the wrong place. The OP seems like a smart cookie, so I figured that would balance my laziness.

Thanks for the correction.
Re: Hierarchy, sort of [message #291035 is a reply to message #290760] Wed, 02 January 2008 16:15 Go to previous messageGo to next message
dukeslater
Messages: 11
Registered: November 2007
Location: Oklahoma USA
Junior Member
Still validating the data, but it looks good so far (6000 employees).

Just wanted to say thanks again for your assistance.
Re: Hierarchy, sort of [message #292672 is a reply to message #290766] Wed, 09 January 2008 10:12 Go to previous messageGo to next message
dukeslater
Messages: 11
Registered: November 2007
Location: Oklahoma USA
Junior Member
Michel -

The connect by path query works perfectly for my needs - but only in test (that data is a couple of months old).

When I run the same query in production, I'm getting the 'connect by loop' error, so I try:

select * from table1 where employee = supervisor


No records returned in either database, so I try:

select * from table1 a, table1 b
where a.employee = b.supervisor
and b.employee = a.supervisor


This returns some records in prod, but actually returns more records in test.

Next I try:

select * from table1 a, table1 b, table1 c
where a.employee = b.supervisor
and b.employee = c.supervisor 
and c.employee = a.supervisor


Same result - some records in prod, but a few more records in test.

I'm baffled - any suggestions on how to troubleshoot this type of error?

Thank you.
Re: Hierarchy, sort of [message #292673 is a reply to message #292672] Wed, 09 January 2008 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Before 10g you have to create your PL/SQL procedure to handle such cases.
In 10g, you have the NOCYCLE option which stops the connect by code.

Regards
Michel
Re: Hierarchy, sort of [message #292675 is a reply to message #292673] Wed, 09 January 2008 10:25 Go to previous messageGo to next message
dukeslater
Messages: 11
Registered: November 2007
Location: Oklahoma USA
Junior Member
I can code around those cases (or have HR correct them), but I don't understand why the query runs in test but not prod.

What is different, and how can I identify it?
Re: Hierarchy, sort of [message #292676 is a reply to message #292675] Wed, 09 January 2008 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know I have neither the data nor the query.

Regards
Michel
Re: Hierarchy, sort of [message #292784 is a reply to message #292676] Wed, 09 January 2008 20:18 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If your data contains loops but the query is not failing, then there is only one logical explanation: your query doesn't hit those rows.

Find one of the "erroneous" rows, run your query, then look for the erroneous row in the output - it shouldn't be there. The reason why not will be:
- You used a START WITH clause and the rows are not connect to the tree.
- You had additional conditions in your CONNECT BY that short-circuited the connect-by before it reached the loop.

Ross Leishman
Re: Hierarchy, sort of [message #293047 is a reply to message #292784] Thu, 10 January 2008 13:29 Go to previous message
dukeslater
Messages: 11
Registered: November 2007
Location: Oklahoma USA
Junior Member
Thank you for your advice. I've eliminated those rows from the query with no ill effects to the results, and in the future I'll use your tips to troubleshoot similar problems (we move to 10g in March, so that will make this type of project much easier).

Thanks again.
Previous Topic: adding zeros to the left of a number in the number column
Next Topic: How to Determine First Function Call vs Subsequent Calls
Goto Forum:
  


Current Time: Sat Dec 03 22:07:47 CST 2016

Total time taken to generate the page: 0.09772 seconds