Home » SQL & PL/SQL » SQL & PL/SQL » Syhthesizing (11.2.0.3.0)
Syhthesizing [message #677445] |
Fri, 20 September 2019 16:49 |
Amine
Messages: 376 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
|
|
|
Re: Syhthesizing [message #677448 is a reply to message #677445] |
Sat, 21 September 2019 01:28 |
|
Michel Cadot
Messages: 68658 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
1/ What is the purpose of "ord" in table "nodes"? How does it come into play in the problem?
2/ How to determine the relation between "<ALL Cs>", "<ALL Ds>" and "<ALL Cs and Ds>"?
3/ Given the answer to 2/, what if there are "<ALL Cs>" and "<ALL Cs and Ds>" and not "<ALL Ds>"?
[Updated on: Sat, 21 September 2019 08:00] Report message to a moderator
|
|
|
|
Re: Syhthesizing [message #677462 is a reply to message #677457] |
Sat, 21 September 2019 14:32 |
Amine
Messages: 376 Registered: March 2010
|
Senior Member |
|
|
ok.
Let's start from the begining.
In the company, the HR system users have to create docuemnts and send them to a set of departements inside the company.
So, for example, in real world, we have departements C1 to C5, R1 to R5 and D1 to D3.
C1 to C5 represent a set of departements, they have the same structure, the same nature.
Same thing for R1 to R5 and D1 to D3, two sets of departements.
Another aspect of this organisation, is that we can group groups of departements. For example, Cs and Ds can be grouped as a single group.
So, when, for example, a docuemnt is issued, and must be sent to 14 recipients, instead of listing them all, we list only the group of recipients
or groups of recipients.
Example : for docuemnt 100, we have to send it to C1 to C5, R1 to R5, D1 to D3 and A1. Now we don't want to print all these recipients.
We don't want to print :
- A1
- C1 + C2 + C3 + C4 + C5
- R1 + R2 + R3 + R4 + R5
- D1 + D2 + D3
we want to print
- A1
- All Cs and Ds
- All Rs
or
- A1
- All Cs
- All Ds
- All Rs
Example : for docuemnt 200, we have to send it to C1, C3, C5, R1 to R5, D1 to D3 and A1. Now we don't want to print all these recipients.
We don't want to print :
- A1
- C1 + C3 + C5
- R1 + R2 + R3 + R4 + R5
- D1 + D2 + D3
we want to print
- A1
- C1 + C3 + C5
- All Ds
- All Rs
And here comes the order of the display. So, there is a default order of these recipients (column ord in the nodes table).
And in some cases, the HR system users can choose a specific order for a specific situation (column ord in the destinat table).
I hope it's more clear now.
Thanks in advance,
Amine
|
|
|
|
|
Re: Syhthesizing [message #677685 is a reply to message #677445] |
Sun, 06 October 2019 16:44 |
Amine
Messages: 376 Registered: March 2010
|
Senior Member |
|
|
Hi all.
It seems to be not clear, right ? Here is a try :
First, we add these values :
insert into nodes_hierarchy values (18,null);
insert into nodes_hierarchy values (17,null);
insert into nodes_hierarchy values (12,null);
Second, I create a view just to show "the rules", how nodes are organized :
create or replace view v_nodes as
with v as
(
select
nodes.id_node
, nodes.lib_node
, nodes.ord
, nodes_sup.id_node id_node_sup
, nodes_sup.lib_node lib_node_sup
, level lvl
from nodes, nodes_hierarchy, nodes nodes_sup
where 1 = 1
and nodes.id_node = nodes_hierarchy.id_node
and nodes_sup.id_node(+) = nodes_hierarchy.id_node_sup
connect by prior nodes_hierarchy.id_node = id_node_sup
)
select v.*
, count(*) over (partition by id_node_sup, lvl) nb_nodes
from v
where 1 = 1
order by id_node
;
And then for each destinat, we print recipients :
with v000 as
(
select *
from v_nodes
where 1 = 1
and lvl = 1
-- and ( lvl = 1 and :p_group_recipients = 'no' or :p_group_recipients = 'yes' and ...)
), v001 as
(
select
id_destinat
, id_node_sup
, count(*) nb_nodes_destinat
, listagg(lib_node, '+') within group (order by id_node_sup, nvl(destinat.ord, v000.ord)) pre_list_recipients
from destinat, v000
where 1 = 1
and destinat.id_node = v000.id_node
group by
id_destinat
, id_node_sup
), v002 as
(
select v001.*
, case
when nb_nodes_destinat = v000.nb_nodes then lib_node_sup
else pre_list_recipients
end list_recipients
, v000.ord
from v001, v000
where 1 = 1
and v001.id_node_sup = v000.id_node_sup(+)
)
select
distinct
id_destinat
, id_node_sup
, list_recipients
from v002
where 1 = 1
order by 1, id_node_sup
;
Then, we obtain this result :
100 6 <All Cs>
100 12 <All Rs>
100 16 <All Ds>
100 - A1
200 6 C1+C3+C5
200 12 <All Rs>
200 16 <All Ds>
200 - A1
OK. It seems to be what we are looking for, but there's a problem on the order and another trick about the lvl value in v_nodes.
You can see in v000 subquery :
-- and ( lvl = 1 and :p_group_recipients = 'no' or :p_group_recipients = 'yes' and ...)
:p_group_recipients is not set because I do not have sqlplus, I am in Oracle Live SQL.
So now, I want to set some conditions on v_nodes so I can get this :
100 17 <All Cs and Ds>
100 12 <All Rs>
100 - A1
200 6 C1+C3+C5
200 12 <All Rs>
200 16 <All Ds>
200 - A1
and the order problem of the rows still remains.
Thanks in advance,
Amine
|
|
|
|
Goto Forum:
Current Time: Sat Jun 15 16:57:36 CDT 2024
|