Home » SQL & PL/SQL » SQL & PL/SQL » Syhthesizing (11.2.0.3.0)
| Syhthesizing [message #677445] |
Fri, 20 September 2019 16:49  |
Amine
Messages: 371
Registered: March 2010
|
Senior Member |
|
|
Hi Gurus !
I missed you, and I just wanted to exercice my self on this problem.
So we have recipients for a mail. And the problem is how we can synthesize them so they can be easily read.
So, we have a hierarchy composed by two sorts of nodes : atomic nodes (recipients) and global nodes (a group of recipients).
The atomic nodes are the leafs of the hierarchy and the global nodes are not and cant be.
drop table nodes;
create table nodes
(
id_node number ,
lib_node varchar2(100) ,
ord number
)
;
alter table nodes add constraint PK_nodes primary key (id_node);
insert into nodes values (1, 'C1', 1);
insert into nodes values (2, 'C2', 2);
insert into nodes values (3, 'C3', 3);
insert into nodes values (4, 'C4', 4);
insert into nodes values (5, 'C5', 5);
insert into nodes values (6, '<ALL Cs>', 1);
insert into nodes values (7, 'R1', 1);
insert into nodes values (8, 'R2', 2);
insert into nodes values (9, 'R3', 3);
insert into nodes values (10, 'R4', 4);
insert into nodes values (11, 'R5', 5);
insert into nodes values (12, '<ALL Rs>', 2);
insert into nodes values (13, 'D1', 1);
insert into nodes values (14, 'D2', 2);
insert into nodes values (15, 'D3', 3);
insert into nodes values (16, '<ALL Ds>', 1);
insert into nodes values (17, '<ALL Cs and Ds>', 1);
insert into nodes values (18, 'A1', 0);
drop table nodes_hierarchy;
create table nodes_hierarchy
(
id_node number ,
id_node_sup number
);
alter table nodes_hierarchy add constraint pk_nh primary key (id_node);
alter table nodes_hierarchy add constraint fk_nh_node foreign key (id_node) references nodes(id_node);
alter table nodes_hierarchy add constraint fk_nh_node_sup foreign key (id_node_sup) references nodes(id_node);
insert into nodes_hierarchy values (1,6);
insert into nodes_hierarchy values (2,6);
insert into nodes_hierarchy values (3,6);
insert into nodes_hierarchy values (4,6);
insert into nodes_hierarchy values (5,6);
insert into nodes_hierarchy values (7,12);
insert into nodes_hierarchy values (8,12);
insert into nodes_hierarchy values (9,12);
insert into nodes_hierarchy values (10,12);
insert into nodes_hierarchy values (11,12);
insert into nodes_hierarchy values (13,16);
insert into nodes_hierarchy values (14,16);
insert into nodes_hierarchy values (15,16);
insert into nodes_hierarchy values (6,17);
insert into nodes_hierarchy values (16,17);
drop table destinat;
create table destinat
(
id_destinat number ,
id_node number ,
ord number
)
;
-- ===================================== destinat 100
insert into destinat values (100, 1, 1);
insert into destinat values (100, 2, 2);
insert into destinat values (100, 3, 3);
insert into destinat values (100, 4, 4);
insert into destinat values (100, 5, 5);
insert into destinat values (100, 7, 1);
insert into destinat values (100, 8, 2);
insert into destinat values (100, 9, 3);
insert into destinat values (100, 10, 4);
insert into destinat values (100, 11, 5);
insert into destinat values (100, 13, 1);
insert into destinat values (100, 14, 2);
insert into destinat values (100, 15, 3);
insert into destinat values (100, 18, 1);
Here is the dezired output for a set of recipients :
output for destinat 100
=======================
100 A1
100 <ALL Cs and Ds>
100 <ALL Rs>
for this specific group of recipients, we want also, as a different scenario, to set a certain session parameter (to be defined) so we can
group Cs and Ds or not.
Exemple, we set a session parameter, then we have :
100 A1
100 <ALL Cs>
100 <ALL Ds>
100 <ALL Rs>
-- ===================================== destinat 200
insert into destinat values (200, 1, 1);
insert into destinat values (200, 3, 3);
insert into destinat values (200, 5, 5);
insert into destinat values (200, 7, 1);
insert into destinat values (200, 8, 2);
insert into destinat values (200, 9, 3);
insert into destinat values (200, 10, 4);
insert into destinat values (200, 11, 5);
insert into destinat values (200, 13, 1);
insert into destinat values (200, 14, 2);
insert into destinat values (200, 15, 3);
insert into destinat values (200, 18, 1);
output for destinat 200
=======================
200 A1
200 C1+C3+C5+<ALL Ds>
200 <ALL Rs>
Thanks in advance
Amine
|
|
|
|
Current Time: Fri Apr 19 11:37:25 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
