Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchical Data
Hierarchical Data [message #197241] |
Tue, 10 October 2006 13:38  |
yerics
Messages: 89 Registered: August 2006
|
Member |
|
|
I have the following table and insert script. Here for eg,
I want to display all records of NANCY WALTER.
The c_MGRID is blank for 2nd and 3rd record of NANCY WALTER.
create table EMPINFO
( c_STATUS VARCHAR2(2),
c_GROUP VARCHAR2(10),
c_SYSID VARCHAR2(10),
c_SYSTEM VARCHAR2(10),
c_NAME VARCHAR2(40),
c_ID VARCHAR2(10),
c_MGRID VARCHAR2(10),
c_MGRNAME VARCHAR2(40)
);
Typical Data is
insert into EMPINFO VALUES ('A','HR','','','JASON BICKER','0000001','','');
insert into EMPINFO VALUES ('A','HR','','','NANCY WALTER','0400001','0000001','JASON BICKER');
insert into EMPINFO VALUES ('','MF','NAN3005','WIN','NANCY WALTER','0400001','','');
insert into EMPINFO VALUES ('','MF','NAN3005','UNIX','NANCY WALTER','0400001','','');
insert into EMPINFO VALUES ('A','HR','','','ADAM BRYAN','0400002','0000001','JASON BICKER');
insert into EMPINFO VALUES ('','MF','BRY0034','WIN','ADAM BRYAN','0400002','','');
insert into EMPINFO VALUES ('A','HR','','','STING CORY','0400003','0400002','ADAM BRYAN');
insert into EMPINFO VALUES ('','MF','STI4040','UNIX','STING CORY','0400003','','');
start with and connect by prior only displays the 1st row because there is a relationship between mgr id and emp id.
|
|
|
|
Re: Hierarchical Data [message #197251 is a reply to message #197249] |
Tue, 10 October 2006 14:16  |
yerics
Messages: 89 Registered: August 2006
|
Member |
|
|
Well I need a similar output for all employees reporting to their respective managers starting with JASON BICKER. If i provide the following query.]
SELECT c_mgrname, c_name, c_status, c_group, c_sysid, c_system FROM EMPINFO
START WITH c_id = '0000001' CONNECT BY PRIOR c_id = c_mgrid;
The O/p is as below. In this case only one record of NANCY WALTER comes up. Similar only one record of Adam Bryan comes even though the table contains 2 records.
C_MGRNAME C_NAME C_ C_GROUP C_SYSID C_SYSTEM
--------------- --------------- -- ---------- ---------- ----------
JASON BICKER A HR
JASON BICKER NANCY WALTER A HR
JASON BICKER ADAM BRYAN A HR
ADAM BRYAN STING CORY A HR
|
|
|
Goto Forum:
Current Time: Wed Feb 19 05:31:13 CST 2025
|