-
Hierarchial Query is not displaying the rows in a hierarchial manner
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');
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|