Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchy problem (release 1102000100)
Hierarchy problem [message #595100] Fri, 06 September 2013 12:14 Go to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

Hi all,
We have a hierarchy between elements. And items are assigned to these elements.
Each element has a theoretical number of occurrences. And we can assign different items to the same elements.

So here is the hierarchy table of elements :
drop table relation
/
create table relation
(
  elmt      number(4)   ,
  elmt_sup  number(4)   ,
  theo      number(6)
)
/

insert into relation values (10, null, 1);
insert into relation values (20, 10, 1);
insert into relation values (30, 20, 3);
insert into relation values (40, 20, 1);
insert into relation values (50, 40, 2);
insert into relation values (60, 20, 2);
insert into relation values (70, 10, 1);    
insert into relation values (80, 70, 1);    
insert into relation values (90, 70, 2);    
insert into relation values (100, 10, 1); 


And here is the item table where we assign elements to items.
drop table item
/
create table item
(
  id        varchar2(50)   ,
  elmt      number(4)
)
/

insert into item values ('master', 10);
insert into item values ('item200', 20);
insert into item values ('item201', 20);
insert into item values ('item301', 30);
insert into item values ('item302', 30);
insert into item values ('item303', 30);
insert into item values ('item304', 30);


I want to write a query that shows a sort of a resume of these two tables. I wrote it but there is a little lack :
with elmtl as
(
  select 
  rpad(' ', level * 3, ' ') || elmt || ' (' || theo || ')' lib_elmt
  , rownum rn
  , elmt
  , theo
  from relation
  , table (cast (multiset (select level from dual connect by level <= theo / (select nvl(theo,1) from relation a where elmt = relation.elmt_sup)) as sys.OdciNumberList ) )
  start with elmt_sup is null
  connect by prior elmt = elmt_sup
  order siblings by elmt
), elmtl2 as
(
  select 
  elmtl.*
  , row_number() over (partition by elmt order by null) ordre
  from elmtl
  where 1 = 1
  order by rn
)
, my_item as
(
  select 
  id
  , row_number() over (partition by elmt order by id) ordre
   , count(id) over (partition by elmt order by null) really
 , elmt
  from item
  where 1 = 1
)
select 
lib_elmt 
|| ' (' || nvl(really,0) || '/' || theo || ') ' 
|| elmtl2.ordre 
|| ' : ' || id
from
elmtl2
, my_item item
where 1 = 1
and elmtl2.elmt = item.elmt(+)
and elmtl2.ordre = item.ordre(+)
/


Here is the output :
   10 (1) (1/1) 1 : master
      20 (1) (2/1) 1 : item200
         30 (3) (4/3) 1 : item301
         30 (3) (4/3) 2 : item302
         30 (3) (4/3) 3 : item303
         40 (1) (0/1) 1 :
            50 (2) (0/2) 1 :
            50 (2) (0/2) 2 :
         60 (2) (0/2) 1 :
         60 (2) (0/2) 2 :
      70 (1) (0/1) 1 :
         80 (1) (0/1) 1 :
         90 (2) (0/2) 1 :
         90 (2) (0/2) 2 :
      100 (1) (0/1) 1 :

15 rows selected.


item201 and item304 are not displayed. As we can remark, this is because we have assigned 2 occurrences to element 20 (and theo(20) = 1), and assigned 4 occurrences to element 30 (and theo(30) = 3).
How can we modify the above query so we have this output :

   10 (1) (1/1) 1 : master
      20 (1) (2/1) 1 : item200
      20 (1) (2/1) 2 : item201 *
         30 (3) (4/3) 1 : item301
         30 (3) (4/3) 2 : item302
         30 (3) (4/3) 3 : item303
         30 (3) (4/3) 4 : item304 *
         40 (1) (0/1) 1 :
            50 (2) (0/2) 1 :
            50 (2) (0/2) 2 :
         60 (2) (0/2) 1 :
         60 (2) (0/2) 2 :
      70 (1) (0/1) 1 :
         80 (1) (0/1) 1 :
         90 (2) (0/2) 1 :
         90 (2) (0/2) 2 :
      100 (1) (0/1) 1 :

17 rows selected.


Thanks in advance,
Amine
Re: Hierarchy problem [message #595101 is a reply to message #595100] Fri, 06 September 2013 12:57 Go to previous messageGo to next message
Michel Cadot
Messages: 59118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: Hierarchy problem [message #595103 is a reply to message #595100] Fri, 06 September 2013 13:21 Go to previous messageGo to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

Here is the meaning of each line of the output :
elmnt (x) (y/z) o : item.
elmnt : the element from the relation table.
x : the number(theoretical) of occurrences of the element. So in theory, we assign x items to the element. But in the real world we can have more or less then x.
y : the number of occurrences of items that are assigned to the element.
z : same as x
o : the order of the occurrence.
item : the item that is assigned to the element.

For example : this line
20 (1) (2/1) 1 : item200
Means that the element 20 have been assigned to item200.
2 items have been assigned to the element 20. But in the output the second item doesn't appear.

About your question, I think I've already explained it : the two rows marked with * are not displayed because they exceed the theoretical number. What to do so they will be displayed ?

Amine wrote on Fri, 06 September 2013 18:14
Hi all,
item201 and item304 are not displayed. As we can remark, this is because we have assigned 2 occurrences to element 20 (and theo(20) = 1), and assigned 4 occurrences to element 30 (and theo(30) = 3).
How can we modify the above query so we have this output :

   10 (1) (1/1) 1 : master
      20 (1) (2/1) 1 : item200
      20 (1) (2/1) 2 : item201 *
         30 (3) (4/3) 1 : item301
         30 (3) (4/3) 2 : item302
         30 (3) (4/3) 3 : item303
         30 (3) (4/3) 4 : item304 *
         40 (1) (0/1) 1 :
            50 (2) (0/2) 1 :
            50 (2) (0/2) 2 :
         60 (2) (0/2) 1 :
         60 (2) (0/2) 2 :
      70 (1) (0/1) 1 :
         80 (1) (0/1) 1 :
         90 (2) (0/2) 1 :
         90 (2) (0/2) 2 :
      100 (1) (0/1) 1 :

17 rows selected.


Thanks in advance,
Amine

[Updated on: Fri, 06 September 2013 13:24]

Report message to a moderator

Re: Hierarchy problem [message #595124 is a reply to message #595103] Sat, 07 September 2013 05:12 Go to previous messageGo to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

Is the problem still not clear ?
Re: Hierarchy problem [message #595153 is a reply to message #595124] Sat, 07 September 2013 17:23 Go to previous messageGo to next message
mikomi
Messages: 33
Registered: July 2013
Member
http://en.wikipedia.org/wiki/Gobbledygook or http://en.wikipedia.org/wiki/Gibberish, take your pick Laughing
Re: Hierarchy problem [message #595227 is a reply to message #595153] Mon, 09 September 2013 08:51 Go to previous messageGo to next message
Maaher
Messages: 7053
Registered: December 2001
Senior Member
Hi,

I've been looking into this. In the script below I've added a calculated "key02" field to join item and relation records.

Here's the approach in short:
1. Generate enough duplicates in the relation table based on your "theo" column and the occurrences in the item table
We need to distinct it, since the connect by construction would generate a lot of duplicates

2. Use the query of step 1 to add a hierarchy. Note the use of the key02 column to avoid duplicates

3. Add the item table ( via an inner select including a new "key02" column, so we can make sure that each item is joined only once)

Disclaimer: I've written this quickly while I was waiting for a batch to end, so it's kind of "quick and dirty" but it should get you started.

Enjoy!

create table mhe_relation
(
  elmt      number(4)   ,
  elmt_sup  number(4)   ,
  theo      number(6)
)
/
set feedback off
insert into mhe_relation values (10, null, 1);
insert into mhe_relation values (20, 10, 1);
insert into mhe_relation values (30, 20, 3);
insert into mhe_relation values (40, 20, 1);
insert into mhe_relation values (50, 40, 2);
insert into mhe_relation values (60, 20, 2);
insert into mhe_relation values (70, 10, 1);    
insert into mhe_relation values (80, 70, 1);    
insert into mhe_relation values (90, 70, 2);    
insert into mhe_relation values (100, 10, 1); 
set feedback on

create table mhe_item
(
  id        varchar2(15)   ,
  elmt      number(4)
)
/
set feedback off
insert into mhe_item values ('master', 10);
insert into mhe_item values ('item200', 20);
insert into mhe_item values ('item201', 20);
insert into mhe_item values ('item301', 30);
insert into mhe_item values ('item302', 30);
insert into mhe_item values ('item303', 30);
insert into mhe_item values ('item304', 30);
set feedback on

Prompt 1. Make sure we have enough copies (based on theo AND items) per element
Select distinct 
       rel.elmt
     , rel.elmt_sup
     , rel.theo
     , level lvl
From   mhe_relation rel
Connect By level <= Greatest(rel.theo, ( Select count(*) From mhe_item where elmt = rel.elmt))
Order By elmt
/

Prompt 2. Add hierarchy
Select r.elmt
     , r.theo
     , r.key02   key02
     , level   lvl
From   ( Select distinct 
                rel.elmt
              , rel.elmt_sup
              , rel.theo
              , level key02
         From   mhe_relation rel
         Connect By level <= Greatest(rel.theo, ( Select count(*) From mhe_item where elmt = rel.elmt))
         Order By rel.elmt
       ) r
Connect by prior r.elmt = r.elmt_sup
And     prior r.key02 = 1
Start With r.elmt_sup Is Null
/

Prompt 3. Add item descriptions + format
Column thestring format a30
Select  lpad(r.elmt, level*3, ' ')||' ('||r.theo||') ('
      ||count(i.elmt) over ( partition by r.elmt)||'/'|| r.theo ||') '
      ||r.key02||': '||i.id thestring
From   ( Select distinct
                rel.elmt
              , rel.elmt_sup
              , rel.theo
              , level key02
         From   mhe_relation rel
         Connect By level <= Greatest(rel.theo, ( Select count(*) From mhe_item where elmt = rel.elmt))
         Order By rel.elmt
       ) r
   ,   ( Select elmt
              , id
              , row_number() over ( partition by elmt order by id) key02
         From   mhe_item
       ) i
Where   r.elmt = i.elmt(+)
And     r.key02  = i.key02(+)       
Connect by prior r.elmt = r.elmt_sup
And     prior r.key02 = 1
Start With r.elmt_sup Is Null
Order By r.elmt, r.key02
/

drop table mhe_relation purge
/

drop table mhe_item purge
/


MHE
Re: Hierarchy problem [message #595964 is a reply to message #595227] Mon, 16 September 2013 12:33 Go to previous message
Amine
Messages: 264
Registered: March 2010
Senior Member

Thank you Maher for your support.
The logic you used seems to be good. The key of your solution resides in this connect by :
Connect By level <= Greatest(rel.theo, ( Select count(*) From mhe_item where elmt = rel.elmt))

and in this
And     prior r.key02 = 1


Thank you again mister Maher !
Previous Topic: PLease help me to send the result of this procedure to .net code using single OUT parameter.
Next Topic: large record update
Goto Forum:
  


Current Time: Wed Sep 17 23:42:52 CDT 2014

Total time taken to generate the page: 0.13300 seconds