Home » SQL & PL/SQL » SQL & PL/SQL » Syhthesizing (11.2.0.3.0)
Syhthesizing [message #677445] Fri, 20 September 2019 16:49 Go to next message
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
Re: Syhthesizing [message #677448 is a reply to message #677445] Sat, 21 September 2019 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
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 #677457 is a reply to message #677445] Sat, 21 September 2019 05:48 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
It would be very helpful if you describe in more detail exactly what each data element means, what it represents in the real world, and how you anticipat it being used. With what little we have at the moment, it looks like you have a seriously flawed data model.
Re: Syhthesizing [message #677462 is a reply to message #677457] Sat, 21 September 2019 14:32 Go to previous messageGo to next message
Amine
Messages: 371
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 #677464 is a reply to message #677462] Sun, 22 September 2019 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This does not answer my questions 2 and 3.

Quote:
we want to print ... or ...
A query returns only one option, how to know if it is first or second part of your "or"?

Re: Syhthesizing [message #677578 is a reply to message #677464] Thu, 26 September 2019 15:38 Go to previous messageGo to next message
Amine
Messages: 371
Registered: March 2010
Senior Member

Michel Cadot wrote on Sun, 22 September 2019 08:35

This does not answer my questions 2 and 3.

Quote:
we want to print ... or ...
A query returns only one option, how to know if it is first or second part of your "or"?

I've mentionned it in the initial post.

we want to print, according to a variable or a session variable when it's set to something or not, the recipients like this way :

set session parameter p_group_recipients=yes
<execute query>
- A1
- All Cs and Ds
- All Rs

or
set session parameter p_group_recipients=no
<execute query>
- A1
- All Cs
- All Ds
- All Rs

Let's say for this post, without creating a context and a package to set session variables, let's say it's a sqlplus variable

var :p_group_recipients varchar2(100)

and use it in the query.
Re: Syhthesizing [message #677685 is a reply to message #677445] Sun, 06 October 2019 16:44 Go to previous messageGo to next message
Amine
Messages: 371
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

Re: Syhthesizing [message #677855 is a reply to message #677445] Tue, 15 October 2019 16:38 Go to previous message
Amine
Messages: 371
Registered: March 2010
Senior Member

Hi again !
Is my requirement so difficult for gurus like @Barbara Boehmer or @Michel Cadot ???
Previous Topic: Why calculated a later invalid statement in a case-when after first true clause found?
Next Topic: Calling an API from PL/SQL
Goto Forum:
  


Current Time: Thu Mar 28 11:02:10 CDT 2024