Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchical query, rolling up value from child to parent (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0)
Hierarchical query, rolling up value from child to parent [message #619782] |
Fri, 25 July 2014 01:08 |
|
hendrif2
Messages: 6 Registered: July 2014
|
Junior Member |
|
|
Hi
I have the resultset below :-
drop table tStatus;
create table tStatus(
id number
, pid number
, name varchar2(20)
, status varchar2(1)
, result varchar2(4)
);
insert into tStatus values(1,null, 'A', 'N', null);
insert into tStatus values(2, 1, 'B', 'Y', 'PASS');
insert into tStatus values(3, 2, 'C', 'Y', 'FAIL');
commit;
select * from tStatus
connect by prior id = pid
start with pid is null;
ID PID NAME STATUS RESULT
---------- ---------- -------------------- ------ ------
1 A N
2 1 B Y PASS
3 2 C Y FAIL
I would like to rollup a child's Result value to it's parent element if the Status column is set to "Y".
If the Status column is not "Y", then it should not rollup any further, I have added example output
to assist with explanation.
This is an example of the desired output for the information above:-
ID PID NAME STATUS RESULT
---------- ---------- -------------------- ------ ------
1 A N FAIL
2 1 B Y FAIL
3 2 C Y FAIL
Another example :-
truncate table tStatus;
insert into tStatus values(1,null, 'A', 'N', null);
insert into tStatus values(2, 1, 'B', 'N', 'PASS');
insert into tStatus values(3, 2, 'C', 'Y', 'FAIL');
commit;
select * from tStatus
connect by prior id = pid
start with pid is null;
ID PID NAME STATUS RESULT
---------- ---------- -------------------- ------ ------
1 A N
2 1 B N PASS
3 2 C Y FAIL
Desired output :-
ID PID NAME STATUS RESULT
---------- ---------- -------------------- ------ ------
1 A N
2 1 B N FAIL
3 2 C Y FAIL
Any help would be greatly appreciated.
Regards
Faadiel
|
|
|
|
|
|
|
Re: Hierarchical query, rolling up value from child to parent [message #619811 is a reply to message #619810] |
Fri, 25 July 2014 02:41 |
|
Michel Cadot
Messages: 68645 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
I merged both examples in one test case:
create table tStatus(
id number
, pid number
, name varchar2(20)
, status varchar2(1)
, result varchar2(4)
);
insert into tStatus values(1,null, 'A', 'N', null);
insert into tStatus values(2, 1, 'B', 'Y', 'PASS');
insert into tStatus values(3, 2, 'C', 'Y', 'FAIL');
insert into tStatus values(4,null, 'A', 'N', null);
insert into tStatus values(5, 4, 'B', 'N', 'PASS');
insert into tStatus values(6, 5, 'C', 'Y', 'FAIL');
commit;
I added 2 columns in the output to show how the query works: the original result and the previous status:
SQL> select id, pid,name, status, result,
2 decode(prior status,
3 'N', result,
4 last_value(nullif(result,'PASS') ignore nulls)
5 over (partition by connect_by_root id order by id desc))
6 result,
7 prior status
8 from tStatus
9 connect by prior pid = id
10 start with id not in (select nvl(pid,-999) from tstatus)
11 order by connect_by_root id, id
12 /
ID PID NAME S RESU RESU P
---------- ---------- -------------------- - ---- ---- -
1 A N FAIL Y
2 1 B Y PASS FAIL Y
3 2 C Y FAIL FAIL
4 A N N
5 4 B N PASS FAIL Y
6 5 C Y FAIL FAIL
|
|
|
|
|
|
Re: Hierarchical query, rolling up value from child to parent [message #619858 is a reply to message #619815] |
Fri, 25 July 2014 10:09 |
|
Michel Cadot
Messages: 68645 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Sorry for late, I was off, yes, I also found there is a bug in 10.2.0.4 as I didn't version your version I posted a copy of my 11.2.0.1 session.
In 10.2.0.4, you can use the following workaround:
SQL> with
2 data as (
3 select id, pid, name, status, result, prior status pstatus, connect_by_root id rid
4 from tStatus
5 connect by prior pid = id
6 start with id not in (select nvl(pid,-999) from tstatus)
7 )
8 select id, pid, name, status,
9 decode(pstatus,
10 'N', result,
11 last_value(nullif(result,'PASS') ignore nulls)
12 over (partition by rid order by id desc))
13 result,
14 pstatus
15 from data
16 order by rid, id
17 /
ID PID NAME S RESU P
---------- ---------- -------------------- - ---- -
1 A N FAIL Y
2 1 B Y FAIL Y
3 2 C Y FAIL
4 A N N
5 4 B N FAIL Y
6 5 C Y FAIL
Also I concentrate on FAIL status an omit the case where all is PASS, I think (but didn't check) a little bit of modification is required in this case.
Edit: my query also assumes that there can't be any Y status before a N one (in the order from pid=null to leaf displayed here). This also leads to a question (I didn't check) does the query works if all status are Y?
So the importance of a good test case.
[Updated on: Fri, 25 July 2014 10:14] Report message to a moderator
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 00:09:01 CDT 2024
|