Home » SQL & PL/SQL » SQL & PL/SQL » Connect by to Get Parent and Supervisor Details in Oracle (Oracle 9i/10g)
| Connect by to Get Parent and Supervisor Details in Oracle [message #598554] |
Tue, 15 October 2013 13:36  |
 |
ashagang
Messages: 10
Registered: December 2012
|
Junior Member |
|
|
Hi,
I have a table like below:
EMP_ID MGR_ID EMP_NAME EMP_ROLE EMP_HIERARCHY
10 9 John Developer 7,8,9,10
9 8 Charlie Manager 7,8,9
8 7 Bruce GL 7,8
7 King CEO 7
20 18 Jack Developer 7,16,17,18,20
18 17 Adam Teamlead 7,16,17,18
17 16 Erik Manager 7,16,17
16 7 David GL 7,16
We can see that the for each employee, there is a corresponding Manager tagged except for CEO of the company.
Now, I want the output to be like below
EMP_ID EMP_NAME EMP_ROLE MGR_ID MGR_NAME MGR_ROLE SUPERVISOR_ID SUPERVISOR_NAME SUPERVISOR_ROLE
Here, MGR_ID is nothing but the MGR_ID from the same row and Supervisor_id is nothing but the MGR_ID of MGR_ID
Ex:- For 10 EMP_ID, the MGR_ID = 9 and Supervisor_id = Mgrid of 9 i.e., 8
For 8, the Mgr_id = 7 and Supervisor_id = Mgrid of 7 which is none. etc.,
The same logic applies for Mgr_name, Mgr_Role , Supervisor_name and Supervisor_role too.
I could not format the data in a tabular format due to some formatting issues.
Could someone please help to develop the logic using connect by function in Oracle?
Asha
|
|
|
|
|
|
|
|
| Re: Connect by to Get Parent and Supervisor Details in Oracle [message #598560 is a reply to message #598559] |
Tue, 15 October 2013 13:50  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
ashagang wrote on Wed, 16 October 2013 00:18
But the problem is, most of the examples for Connect by explains from top level to down level i.e., from CEO to Developer.
But, for my logic, i want the bottom up approach i.e., from Developer to CEO
You just need to reverse the conditions around the "=" operator in the CONNECT BY PRIOR clause to do that.
For a better help, you should post the create table scripts, insert statements with some test data, and your test case which you run in SQL*Plus. Copy paste the output. It will be so easy to assist you after that.
[Updated on: Tue, 15 October 2013 13:52] Report message to a moderator |
|
|
|
| Re: Connect by to Get Parent and Supervisor Details in Oracle [message #598565 is a reply to message #598560] |
Tue, 15 October 2013 14:13 |
|
ashagang
Messages: 10
Registered: December 2012
|
Junior Member |
|
|
CREATE TABLE EMP_HIERARCHY_TEST
(
EMP_ID NUMBER,
MGR_ID NUMBER,
EMP_NAME VARCHAR2(50 BYTE),
EMP_ROLE VARCHAR2(50 BYTE),
EMP_HIERARCHY VARCHAR2(100 BYTE)
);
Insert statements are below:
SET DEFINE OFF;
Insert into EMP_HIERARCHY_TEST(EMP_ID,MGR_ID,EMP_NAME,EMP_ROLE,EMP_HIERARCHY) Values (10, 9, 'John', 'Developer', '7,8,9,10');
Insert into EMP_HIERARCHY_TEST(EMP_ID,MGR_ID,EMP_NAME,EMP_ROLE,EMP_HIERARCHY) Values (9, 8, 'Charlie', 'Manager', '7,8,9');
Insert into EMP_HIERARCHY_TEST(EMP_ID,MGR_ID,EMP_NAME,EMP_ROLE,EMP_HIERARCHY) Values (8, 7, 'Bruce', 'GL', '7,8');
Insert into EMP_HIERARCHY_TEST(EMP_ID,MGR_ID,EMP_NAME,EMP_ROLE,EMP_HIERARCHY) Values (7, 'King', 'CEO', '7');
Insert into EMP_HIERARCHY_TEST(EMP_ID,MGR_ID,EMP_NAME,EMP_ROLE,EMP_HIERARCHY) Values (20, 18, 'Jack', 'Developer', '7,16,17,18,20');
Insert into EMP_HIERARCHY_TEST(EMP_ID,MGR_ID,EMP_NAME,EMP_ROLE,EMP_HIERARCHY) Values (18, 17, 'Adam', 'Teamlead', '7,16,17,18');
Insert into EMP_HIERARCHY_TEST(EMP_ID,MGR_ID,EMP_NAME,EMP_ROLE,EMP_HIERARCHY) Values (17, 16, 'Erik', 'Manager', '7,16,17');
Insert into EMP_HIERARCHY_TEST(EMP_ID,MGR_ID,EMP_NAME,EMP_ROLE,EMP_HIERARCHY) Values (16, 7, 'David', 'GL', '7,16');
COMMIT;
Now, when i execute the below query, i am getting the parent and parent of parent details not in a single row but in separate rows.
select e.*
from EMP_HIERARCHY_TEST e
connect by nocycle prior mgr_id= emp_id and LEVEL <= 3
start with emp_id = 9--258906 --Rolla
order siblings by emp_id
Is there any way to get everything in single row so that i can get the consolidated data in 1 row.
Hope this helps.
Asha
|
|
|
|
|
|
| Re: Connect by to Get Parent and Supervisor Details in Oracle [message #598567 is a reply to message #598566] |
Tue, 15 October 2013 14:19 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
In addition, your test case is not correct (see the use of [code] tags, isn't it easier to read?):
SQL> CREATE TABLE EMP_HIERARCHY_TEST
2 (
3 EMP_ID NUMBER,
4 MGR_ID NUMBER,
5 EMP_NAME VARCHAR2(50 BYTE),
6 EMP_ROLE VARCHAR2(50 BYTE),
7 EMP_HIERARCHY VARCHAR2(100 BYTE)
8 );
Table created.
SQL> Insert into EMP_HIERARCHY_TEST(EMP_ID,MGR_ID,EMP_NAME,EMP_ROLE,EMP_HIERARCHY) Values (10, 9, 'John', 'Developer', '7,8,9,10');
1 row created.
SQL> Insert into EMP_HIERARCHY_TEST(EMP_ID,MGR_ID,EMP_NAME,EMP_ROLE,EMP_HIERARCHY) Values (9, 8, 'Charlie', 'Manager', '7,8,9');
1 row created.
SQL> Insert into EMP_HIERARCHY_TEST(EMP_ID,MGR_ID,EMP_NAME,EMP_ROLE,EMP_HIERARCHY) Values (8, 7, 'Bruce', 'GL', '7,8');
1 row created.
SQL> Insert into EMP_HIERARCHY_TEST(EMP_ID,MGR_ID,EMP_NAME,EMP_ROLE,EMP_HIERARCHY) Values (7, 'King', 'CEO', '7');
Insert into EMP_HIERARCHY_TEST(EMP_ID,MGR_ID,EMP_NAME,EMP_ROLE,EMP_HIERARCHY) Values (7, 'King', 'CEO', '7')
*
ERROR at line 1:
ORA-00947: not enough values
SQL> Insert into EMP_HIERARCHY_TEST(EMP_ID,MGR_ID,EMP_NAME,EMP_ROLE,EMP_HIERARCHY) Values (20, 18, 'Jack', 'Developer', '7,16,17,18,20');
1 row created.
SQL> Insert into EMP_HIERARCHY_TEST(EMP_ID,MGR_ID,EMP_NAME,EMP_ROLE,EMP_HIERARCHY) Values (18, 17, 'Adam', 'Teamlead', '7,16,17,18');
1 row created.
SQL> Insert into EMP_HIERARCHY_TEST(EMP_ID,MGR_ID,EMP_NAME,EMP_ROLE,EMP_HIERARCHY) Values (17, 16, 'Erik', 'Manager', '7,16,17');
1 row created.
SQL> Insert into EMP_HIERARCHY_TEST(EMP_ID,MGR_ID,EMP_NAME,EMP_ROLE,EMP_HIERARCHY) Values (16, 7, 'David', 'GL', '7,16');
1 row created.
SQL> COMMIT;
Commit complete.
In addition, a field should NOT contain more than one value.
Read Normalization.
[Updated on: Tue, 15 October 2013 14:21] Report message to a moderator |
|
|
|
| Re: Connect by to Get Parent and Supervisor Details in Oracle [message #598570 is a reply to message #598567] |
Tue, 15 October 2013 14:34 |
|
ashagang
Messages: 10
Registered: December 2012
|
Junior Member |
|
|
For the EMP_ID = 7, the below is the correct insert statement.
Insert into EMP_HIERARCHY_TEST(EMP_ID,MGR_ID,EMP_NAME,EMP_ROLE,EMP_HIERARCHY) Values (7,NULL, 'King', 'CEO', '7');
Quote:In addition, a field should NOT contain more than one value.
Read Normalization.
If you are referring to EMP_HIERARCHY field here, it does contain the hierarchy details from the given EMP_ID to root.
|
|
|
|
| Re: Connect by to Get Parent and Supervisor Details in Oracle [message #598571 is a reply to message #598570] |
Tue, 15 October 2013 14:40 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
ashagang wrote on Wed, 16 October 2013 01:04
If you are referring to EMP_HIERARCHY field here, it does contain the hierarchy details from the given EMP_ID to root.
You did not get Michel's point. You do not have to keep all the different values in a single column. You can run a simple query to get it at one place anytime. Plus, normalization would keep things perfectly in the DB, that's an industry standard.
|
|
|
|
|
|
|
|
| Re: Connect by to Get Parent and Supervisor Details in Oracle [message #598578 is a reply to message #598573] |
Tue, 15 October 2013 14:58 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
ashagang wrote on Wed, 16 October 2013 01:18The data is already normalized.
Asha,
Folks here are trying to make things much simpler and better for you, not only temporarily, but, permanently. Why do you think the EMP_HIERARCHY field is at all needed with different values stored in a comma separated form? Normalization is a standard concept to make things simpler.
The first normal form itself says that you should create separate tables for each group of related data and identify each row with a unique column or set of columns.
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 21:23:10 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
