Home » SQL & PL/SQL » SQL & PL/SQL » about hierarchy
about hierarchy [message #246649] Thu, 21 June 2007 09:40 Go to next message
beetel
Messages: 96
Registered: April 2007
Member
create table temp_hierarchy (process varchar2(20), depends_on_process varchar2(20));  
insert into temp_hierarchy values ('DEL','PDEL');
insert into temp_hierarchy values ('ARMP','DEL');
insert into temp_hierarchy values ('ARMP','LOAD');
insert into temp_hierarchy values ('IRMP','LOAD');
insert into temp_hierarchy values ('IRMP','DEL');
insert into temp_hierarchy values ('IRMP','ARMP');
insert into temp_hierarchy values ('CRMP','LOAD');
insert into temp_hierarchy values ('CRMP','DEL');
insert into temp_hierarchy values ('CRMP','IRMP');
insert into temp_hierarchy values ('LD','ARMP');
insert into temp_hierarchy values ('LD','IRMP');
insert into temp_hierarchy values ('LD','CRMP');
commit;

SELECT LEVEL, LPAD(' ', LEVEL*3) || process, depends_on_process
FROM temp_hierarchy
START WITH process = 'DEL'
CONNECT BY PRIOR process = depends_on_process;

LEVEL	LPAD('',LEVEL*3)||PROCESS  DEPENDS_ON_PROCESS
-----   -------------------------  ------------------
1	  DEL	                   PDEL
2	      ARMP	           DEL
3	         IRMP	           ARMP
4	            CRMP	   IRMP
5	               LD	   CRMP
4	            LD	           IRMP
3	         LD	           ARMP
2	      IRMP	           DEL
3	         CRMP	           IRMP
4	            LD	           CRMP
3	         LD	           IRMP
2	      CRMP	           DEL
3	         LD	           CRMP


I want to get the root which is DEL. Now, if DEL is not present, then I should get ARMP. In short, I would like to get the process/processes that has/have the most number of dependents. Please help.


[Updated on: Thu, 21 June 2007 09:41]

Report message to a moderator

Re: about hierarchy [message #246681 is a reply to message #246649] Thu, 21 June 2007 11:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is the ones which "depends_on_process" doesn't exist in "process".

Regards
Michel
Re: about hierarchy [message #246709 is a reply to message #246649] Thu, 21 June 2007 11:49 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
select top_proc, no_elements from
(select substr(path, 2, instr(path, ',', 2)-2) as top_proc, no_elements, rank() over (order by no_elements desc) as rn
from
(SELECT sys_connect_by_path(PROCESS, ',') as path, length(sys_connect_by_path(PROCESS, ',')) - length(replace(sys_connect_by_path(PROCESS, ','), ',', '')) as no_elements   
FROM temp_hierarchy
START WITH process = 'DEL'
CONNECT BY PRIOR process = depends_on_process))
where rn=1


However, I'm not sure how you would ever get an answer other than the root element, so possibly slightly pointless.
Previous Topic: Trigger
Next Topic: Column format
Goto Forum:
  


Current Time: Wed Dec 07 20:27:40 CST 2016

Total time taken to generate the page: 0.05376 seconds