Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchy problem (11.2.0.3)
Hierarchy problem [message #626430] Mon, 27 October 2014 15:23 Go to next message
Amine
Messages: 375
Registered: March 2010
Senior Member
Hi all,
I have this table

create table f_user_rat
(
    typ_hierarchy number(5) ,
    pere          number(5) ,
    fils          number(5)
)
;

insert into f_user_rat values (10, null,1);
insert into f_user_rat values (10, null,6);

insert into f_user_rat values (10, 1,6);

insert into f_user_rat values (10, 1,2);
insert into f_user_rat values (10, 1,3);
insert into f_user_rat values (10, 1,4);
insert into f_user_rat values (10, 1,5);

insert into f_user_rat values (10, 6,2);
insert into f_user_rat values (10, 6,3);
insert into f_user_rat values (10, 6,4);
insert into f_user_rat values (10, 6,5);


Then I run this query : 

column typ_hierarchy format 999
column label format a20
select typ_hierarchy, rpad('-', 3 * level, '-') || fils label
from f_user_rat
where 1 = 1
connect by prior fils = pere and prior typ_hierarchy = typ_hierarchy
start with pere is null
order siblings by typ_hierarchy
/


The result is : 
           10 ---1
           10 ------2
           10 ------3
           10 ------4
           10 ------5
           10 ------6
           10 ---------2
           10 ---------3
           10 ---------4
           10 ---------5
           10 ---6
           10 ------2
           10 ------3
           10 ------4
           10 ------5

15 rows selected.

SQL>


I want this result : 
           10 ---1
           10 ------2
           10 ------3
           10 ------4
           10 ------5
           10 ------6
           10 ---------2
           10 ---------3
           10 ---------4
           10 ---------5

10 rows selected.


Meaning I want to exclude the repetition. Is that possible ?

Thanks in advance,

Amine

Report message to a moderator

Re: Hierarchy problem [message #626433 is a reply to message #626430] Mon, 27 October 2014 15:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How 
10 ---6

is a "repetition" of
10 ------6

?

Quote:
Meaning I want to exclude the repetition. Is that possible ?


What about DISTINCT?

[Updated on: Mon, 27 October 2014 15:46]

Report message to a moderator

Re: Hierarchy problem [message #626442 is a reply to message #626433] Mon, 27 October 2014 23:56 Go to previous messageGo to next message
Amine
Messages: 375
Registered: March 2010
Senior Member
We can't use distinct because of the level.

Report message to a moderator

Re: Hierarchy problem [message #626445 is a reply to message #626442] Tue, 28 October 2014 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So explain your result... and answer my first question.

Report message to a moderator

Re: Hierarchy problem [message #626490 is a reply to message #626430] Tue, 28 October 2014 07:50 Go to previous messageGo to next message
sandeep_orafaq
Messages: 88
Registered: September 2014
Member
According to the hierarchy data posted by you, It is clear that the 1 and 6 both are top in the hierarchy. Also 6 is the element which has parents null and 1 both. which seems to be wierd.

Though if you you really need the Output as you have pasted, you can add one more condition in start with clause. But personally I think this is not the proper way. Can you explain the hierarchy?

SQL> select typ_hierarchy, rpad('-', 3 * level, '-') || fils label
  2  from f_user_rat
  3  where 1 = 1
  4  connect by prior fils = pere and prior typ_hierarchy = typ_hierarchy
  5  start with (pere is null and fils !=6)
  6  order siblings by typ_hierarchy
  7  /
TYP LABEL
--- --------------------
 10 ---1
 10 ------2
 10 ------3
 10 ------4
 10 ------5
 10 ------6
 10 ---------2
 10 ---------3
 10 ---------4
 10 ---------5
10 rows selected

Report message to a moderator

Re: Hierarchy problem [message #626750 is a reply to message #626490] Sat, 01 November 2014 16:09 Go to previous messageGo to next message
Amine
Messages: 375
Registered: March 2010
Senior Member
Ok. My table has to satisfy this requirement : Items 2, 3, 4, 5 can receive data from item 1 and 6. And 6 can also receive data from 1. This is the facts.
Now when we model this through a hierarchy, we get the table and the data I gave.

Now, what I am asking, is a matter of convenience. The output I am looking for, that is this one :
           10 ---1
           10 ------2
           10 ------3
           10 ------4
           10 ------5
           10 ------6
           10 ---------2
           10 ---------3
           10 ---------4
           10 ---------5

10 rows selected.

is more convenient than this one :

           10 ---1
           10 ------2
           10 ------3
           10 ------4
           10 ------5
           10 ------6
           10 ---------2
           10 ---------3
           10 ---------4
           10 ---------5
           10 ---6
           10 ------2
           10 ------3
           10 ------4
           10 ------5

15 rows selected.

SQL>

Report message to a moderator

Re: Hierarchy problem [message #626754 is a reply to message #626750] Sun, 02 November 2014 01:22 Go to previous message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 27 October 2014 21:46
How 
10 ---6

is a "repetition" of
10 ------6

?

Quote:
Meaning I want to exclude the repetition. Is that possible ?


What about DISTINCT?


If you don't care about the numbers of '-' then leave them.

Report message to a moderator

Previous Topic: Please clarify the LOCK doubts
Next Topic: ORA-12012 ORA-01031 ORA-06512
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Apr 24 20:13:59 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.