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: Thu Apr 25 15:38:51 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
Is this idea already on the cards? Or do you manage to have multiple databases with different versions in your laptop, of course it would make the system incredibly slow. I saw John recently upgrading to 12c. Just to see you sharing your wealth of knowledge on latest version and it's features.
