Topic: SQL*Plus and PL/SQL >> Hierarchial Query is not displaying the rows in a hierarchial manner
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: Hierarchial Query is not displaying the rows in a hierarchial manner |
| dksampat |
Posted: Jan 15, 2007 10:41:00 PM |
Total Post: 7
Joined: Jan, 2007
|
I need to display rows in a hierarchial manner. But when i use the below query, I am not getting the row output in a hierarchial manner. See below for the query, sample outputs and a reproducible testcase.
Query
*****
SELECT distinct LPAD('**',2*(LEVEL-1))||T3.ID, T3.SUPERVISOR_ID, T1.SEQ_CODE, T2.SEQ_DESC, SUM(VALUE)
FROM T1 , T2, T3, T4
WHERE T1.SEQ_CODE = 1
AND T1.ID = T2.ID
AND T1.ID = T3.ID
AND T3.GROUP_ID = T4.GROUP_ID
GROUP BY LPAD('**',2*(LEVEL-1))||T3.ID, T3.SUPERVISOR_ID, T1.SEQ_CODE, T2.SEQ_DESC
start with T1.ID='Manager1'
connect by prior T3.ID = T3.SUPERVISOR_ID
order by LPAD('**',2*(LEVEL-1))||T3.ID desc
Current Output
**************
Output that I am getting because of the above query is :
ID Supervisor Seq_Code Seq_Desc Value
---------------------------------------------------------------------------------------------
Manager1 Director1 1 A 100
**Superviosr1 Manager1 1 A 050
**Superviosr2 Manager1 1 A 040
**User1 Superviosr1 1 A 010
**User2 Superviosr2 1 A 010
**User3 Superviosr2 1 A 010
In the above output, User1 must come under Supervisor 1 as he is the Supervisor for User1.
My Desired Output
*****************
Whereas the desired output that I want is :
ID Supervisor Seq_Code Seq_Desc Value
---------------------------------------------------------------------------------------------
Manager1 Director1 1 A 100
**Superviosr1 Manager1 1 A 050
**User1 Superviosr1 1 A 010
**Superviosr2 Manager1 1 A 040
**User2 Superviosr2 1 A 010
**User3 Superviosr2 1 A 010
Testcase
********
create table t1 (id varchar2(100), seq_code number);
create table t2 (id varchar2(100), seq_code number,seq_desc varchar2(100));
create table t3 (id varchar2(100), supervisor_id varchar2(100), group_id varchar2(100));
create table t4 (group_id varchar2(100));
insert into t1 values('Manager1','1');
insert into t1 values('Superviosr1','1');
insert into t1 values('Superviosr2','1');
insert into t1 values('User1','1');
insert into t1 values('User2','1');
insert into t1 values('User3','1');
insert into t2 values('Manager1','1','A');
insert into t2 values('Superviosr1','1','A');
insert into t2 values('Superviosr2','1','A');
insert into t2 values('User1','1','A');
insert into t2 values('User2','1','A');
insert into t2 values('User3','1','A');
insert into t3 values('Manager1','Director1','1');
insert into t3 values('Superviosr1','Manager1','1');
insert into t3 values('Superviosr2','Manager1','1');
insert into t3 values('User1','Superviosr1','1');
insert into t3 values('User2','Superviosr2','1');
insert into t3 values('User3','Superviosr2','1');
insert into t4 values('1');
insert into t4 values('1');
insert into t4 values('1');
insert into t4 values('1');
insert into t4 values('1');
insert into t4 values('1'); |
|
|
 sathish |
| Posted: Jan 17, 2007 02:18:32 AM |  |
|
Total Post: 47
Joined: Mar, 2006
|
try this
i dont know i will help or not
SELECT LPAD(a.supervisor_id, LENGTH(a.supervisor_id)+(LEVEL*4)-4,'*')||' '||a.group_id||' '||b.seq_code||' '||b.seq_desc
AS report_to
FROM t3 a,t2 b
where a.id = b.id
START WITH a.supervisor_id='Director1'
CONNECT BY PRIOR a.id=a.supervisor_id;
any mistake correct me.
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Vinoth Kumar | 357 |
| Gopu Gopi | 340 |
| Gitesh Trivedi | 322 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
