Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchy output (11.2.0.3.0)
Hierarchy output [message #619481] Tue, 22 July 2014 18:32 Go to next message
Amine
Messages: 278
Registered: March 2010
Senior Member

Hi all,

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);

set pagesize0
select sys_connect_by_path(elmt, '/') path
from relation
connect by prior elmt = elmt_sup
/

/20
/20/30
/20/60
/20/40
/20/40/50
/100
/70
/70/80
/70/90
/30
/60
/40
/40/50
/50
/80
/90
/10
/10/20
/10/20/30
/10/20/60
/10/20/40
/10/20/40/50
/10/100
/10/70
/10/70/80
/10/70/90

26 rows selected.


Now, I want to get this output :

|20
/20|30
/20|60
/20|40
|20/40|50
|100
|70
/70|80
/70|90
|30
|60
|40
/40|50
|50
|80
|90
|10
|10|20
|10/20|30
|10/20|60
|10/20|40
/10|20/40|50
/10|100
/10|70
|10/70|80
|10/70|90

26 rows selected.

The logic is that we want the last separator to be | .
So alternatively, we put / then |. And the last must be |.

Thanks in advance,

Amine
Re: Hierarchy output [message #619503 is a reply to message #619481] Wed, 23 July 2014 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In short, you don't care what is the separator but the last one must be |.
Is this correct?
If so then use | for ALL separators.

Re: Hierarchy output [message #619567 is a reply to message #619481] Wed, 23 July 2014 07:03 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2086
Registered: January 2010
Senior Member
So you want separators to alternate but so that last separator is pipe character. If so:

select  case mod(level,2)
          when 1 then regexp_replace(sys_connect_by_path(elmt,'|'),'(\|[^|]+)\|([^|]+)','\1/\2')
          else regexp_replace(sys_connect_by_path(elmt,'/'),'(/[^/]+)/([^/]+)','\1|\2')
        end path
  from  relation
  connect by prior elmt = elmt_sup
/

PATH
--------------------
|20
/20|30
/20|60
/20|40
|20/40|50
|100
|70
/70|80
/70|90
|30
|60

PATH
--------------------
|40
/40|50
|50
|80
|90
|10
/10|20
|10/20|30
|10/20|60
|10/20|40
/10|20/40|50

PATH
--------------------
/10|100
/10|70
|10/70|80
|10/70|90

26 rows selected.

SQL> 


SY.
Re: Hierarchy output [message #619640 is a reply to message #619567] Wed, 23 July 2014 16:41 Go to previous messageGo to next message
Amine
Messages: 278
Registered: March 2010
Senior Member

SQL Rock, SQL Roll, SQL Rock n' Roll !
Bravo SY ! This is exactly what I was looking for !
Re: Hierarchy output [message #620498 is a reply to message #619640] Sun, 03 August 2014 09:29 Go to previous messageGo to next message
Amine
Messages: 278
Registered: March 2010
Senior Member

Hi All,

Always with SQL magic, I need to achieve this requirement :

drop table relation
/
create table relation
(
  elmt      number(4)		,
  elmt_sup  number(4)		,
  bis		varchar2(4)		,
  theo      number(6)
)
/

insert into relation values (10, null, 'a', 1);
insert into relation values (20, 10, 'b', 1);
insert into relation values (30, 20, 'c', 3);
insert into relation values (40, 20, 'd', 1);
insert into relation values (50, 40, 'e', 2);
insert into relation values (60, 20, 'f', 2);
insert into relation values (70, 10, 'g', 1);    
insert into relation values (80, 70, 'h', 1);    
insert into relation values (90, 70, 'i', 2);    
insert into relation values (100, 10, 'j', 1);

set pagesize0
column elmt format 999
column path format A30
column other_path format A30
select 
elmt
, path
--, wanted_path
from
(
	select 
	elmt
	, sys_connect_by_path(elmt, '/') path
	, level lvl
	, max(level) over (partition by elmt) max_lvl
	--, 'some SQL magic' wanted_path
	from relation
	connect by prior elmt = elmt_sup
)
where 1 = 1
and lvl = max_lvl
/

  10 /10                            
  20 /10/20                         |a
  30 /10/20/30                      /a|b
  40 /10/20/40                      /a|b
  50 /10/20/40/50                   |a/b|d
  60 /10/20/60                      /a|b
  70 /10/70                         |a
  80 /10/70/80                      /a|g
  90 /10/70/90                      /a|g
 100 /10/100                        |a


So I still want separators to alternate but so that last separator is pipe character.
But according to the new column bis, we will concatenate the bis values rather than the elmt values starting from the first father.
In addition, we want the longest path.

Thanks in advance,

Amine
Re: Hierarchy output [message #620505 is a reply to message #620498] Sun, 03 August 2014 10:39 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2086
Registered: January 2010
Senior Member
So how this is different from your original task? Just few minor changes:

select  elmt,
        path,
        substr(wanted_path,2) wanted_path
  from  (
  select  elmt,
          sys_connect_by_path(elmt, '/') path,
          level lvl,
          max(level) over (partition by elmt) max_lvl,
          case mod(level,2)
                   when 0 then regexp_replace(sys_connect_by_path(prior bis,'|'),'(\|[^|]+)\|([^|]+)','\1/\2')
                   else regexp_replace(sys_connect_by_path(prior bis,'/'),'(/[^/]+)/([^/]+)','\1|\2')
                 end wanted_path
    from  relation
    connect by prior elmt = elmt_sup
        )
  where lvl = max_lvl
/

ELMT PATH                           WANTED_PATH
---- ------------------------------ --------------------
  10 /10
  20 /10/20                         |a
  30 /10/20/30                      /a|b
  40 /10/20/40                      /a|b
  50 /10/20/40/50                   |a/b|d
  60 /10/20/60                      /a|b
  70 /10/70                         |a
  80 /10/70/80                      /a|g
  90 /10/70/90                      /a|g
 100 /10/100                        |a

10 rows selected.

SQL> 


SY.
Re: Hierarchy output [message #620610 is a reply to message #620505] Mon, 04 August 2014 10:05 Go to previous messageGo to next message
Amine
Messages: 278
Registered: March 2010
Senior Member

Hi Solomon,
Thank you very much for your precious help.
I think you should write an entry about the hierarchical queries like did Michel Cadot about the "Row Generator".

I found that I am really weak at that.
One last question SY, how can we reverse the sys_connect_by_path behaviour to obtain something like this :
ELMT PATH 							WANTED_PATH
---- ------------------------------ --------------------
10 	/10								/10
20 	/10/20 							/20/10
30 	/10/20/30 						/30/20/10
40 	/10/20/40 						/40/20/10
50 	/10/20/40/50 					/50/40/20/10
60 	/10/20/60 						/60/20/10
70 	/10/70  						/70/10
80 	/10/70/80						/80/70/10
90 	/10/70/90 						/90/70/10
100 /10/100							/100/10


Thanks another time, many thanks actually !

Amine
Re: Hierarchy output [message #620625 is a reply to message #620610] Mon, 04 August 2014 11:25 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
how can we reverse the sys_connect_by_path behaviour to obtain something like this


Many years ago I opened an enhancement request at Oracle to add a parameter to sys_connect_by_path so that we can have the result in one way or the other one.
But no news.
Re: Hierarchy output [message #620630 is a reply to message #620625] Mon, 04 August 2014 11:47 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel, goot to know it.

I remember, once Thomas Kyte mentioned similar thing in his site. He tried a lot to convince the Oracle development team to restrict use of WHEN OTHERS bug. After a long wait, Oracle though didn't implement it exactly, however introduced a warning in 11g release.

Let's see in future if your efforts are considered Smile
Re: Hierarchy output [message #620633 is a reply to message #620630] Mon, 04 August 2014 11:57 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you are interested in it it is bug 7412475, opened on 16-Sep-2008, last updated (or rather seen) on 14-May-2012 but no actual news till now.
Tom has more power than me on development team. Smile

Re: Hierarchy output [message #620635 is a reply to message #620633] Mon, 04 August 2014 12:06 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
Thanks for the bug information Michel.

Yes I was interested to know the details. It will help me for my own good and knowledge. By the way, I guess it was 10g DB. Could you let me know the exact version, please.
Re: Hierarchy output [message #620636 is a reply to message #620635] Mon, 04 August 2014 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It was 10.2.0.4 but it does not really matter as this is a function improvement.

Re: Hierarchy output [message #620637 is a reply to message #620636] Mon, 04 August 2014 12:14 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
Thanks Michel. Appreciate the info you provided, should help me and others too.

The reason I asked for exact version is, sometimes the little fixes are not a part of major release.

Thanks again Smile
Re: Hierarchy output [message #620644 is a reply to message #620637] Mon, 04 August 2014 14:28 Go to previous messageGo to next message
Amine
Messages: 278
Registered: March 2010
Senior Member

So, if I understand, there is no other solution to reach my requirement than to write a PL/SQL function, right ? Meaning low performance with context switch ...

[Updated on: Mon, 04 August 2014 14:29]

Report message to a moderator

Re: Hierarchy output [message #620645 is a reply to message #620644] Mon, 04 August 2014 14:58 Go to previous messageGo to next message
Amine
Messages: 278
Registered: March 2010
Senior Member

Thanks to this I could reach my goal :

select  elmt,
        path,
        wanted_path
  from  (
  select  elmt,
          sys_connect_by_path(elmt, '/') path,
          level lvl,
          max(level) over (partition by elmt) max_lvl,
          case mod(level,2)
                   when 0 then regexp_replace('|' || rtrim(reverse(sys_connect_by_path(reverse(to_char(prior bis)),'|')), '|'),'(\|[^|]+)\|([^|]+)','\1/\2')
                   else regexp_replace('/' || rtrim(reverse(sys_connect_by_path(reverse(to_char(prior bis)),'/')), '/'),'(/[^/]+)/([^/]+)','\1|\2')
                 end wanted_path
    from  relation
    connect by prior elmt = elmt_sup
        )
  where lvl = max_lvl
/

[Updated on: Mon, 04 August 2014 16:43]

Report message to a moderator

Re: Hierarchy output [message #620660 is a reply to message #620645] Mon, 04 August 2014 21:00 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2086
Registered: January 2010
Senior Member
REVERSE is undocumented function. And it reverses bytes not characters so you might get wrong results when using multi-byte characters. You could use XQUERY:

select  elmt,
        path,
        xmlquery(
                 'string-join(
                              fn:reverse(
                                         for $s at $i in ora:tokenize($p,"/")
                                           where $i > 1
                                           return $s
                                        ),
                              "/"
                             )'
                 passing path as "p"
                 returning content
                ) reversed_path
  from  (
  select  elmt,
          sys_connect_by_path(elmt, '/') || '/' path,
          level lvl,
          max(level) over (partition by elmt) max_lvl
    from  relation
    connect by prior elmt = elmt_sup
        )
  where lvl = max_lvl
/

ELMT PATH                           REVERSED_PATH
---- ------------------------------ -------------
  10 /10/                           /10
  20 /10/20/                        /20/10
  30 /10/20/30/                     /30/20/10
  40 /10/20/40/                     /40/20/10
  50 /10/20/40/50/                  /50/40/20/10
  60 /10/20/60/                     /60/20/10
  70 /10/70/                        /70/10
  80 /10/70/80/                     /80/70/10
  90 /10/70/90/                     /90/70/10
 100 /10/100/                       /100/10

10 rows selected.

SQL> 


SY.
Re: Hierarchy output [message #620747 is a reply to message #620660] Tue, 05 August 2014 12:22 Go to previous messageGo to next message
Amine
Messages: 278
Registered: March 2010
Senior Member

Thank you Mister Solomon.
But it does still not work to reach my requirement :

select  
elmt
, path
, wanted_path
from  
(
      select  
      elmt
      , level lvl
      , max(level) over (partition by elmt) max_lvl
      ,
      case mod(level,2)
        when 0 then 
          regexp_replace
          (
            xmlquery
            (
                 'string-join(
                              fn:reverse(
                                         for $s at $i in ora:tokenize($p,"\|")
                                           where $i > 1
                                           return $s
                                        ),
                              "|"
                              )'
                 passing sys_connect_by_path(prior bis,'|') as "p"
                 returning content
            )
            ,'(\|[^|]+)\|([^|]+)','\1/\2'
          )
        else 
          regexp_replace
          (
            xmlquery
            (
                 'string-join(
                              fn:reverse(
                                         for $s at $i in ora:tokenize($p,"/")
                                           where $i > 1
                                           return $s
                                        ),
                              "/"
                              )'
                 passing sys_connect_by_path(prior bis,'/') as "p"
                 returning content
            )
            ,'(/[^/]+)/([^/]+)','\1|\2'
          )
      end wanted_path
      from relation
      connect by prior elmt = elmt_sup
)
where lvl = max_lvl
/


When passing a sys_connect_by_path with prior elements it does not work.

Any workaround ?

Thanks in advance,

Amine
Re: Hierarchy output [message #620768 is a reply to message #620747] Tue, 05 August 2014 17:57 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2086
Registered: January 2010
Senior Member
Yes, it looks like PRIOR and XQUERY don't like each other. You could use XMLTABLE. Something like:

with t1 as (
            select   elmt,
                     level lvl,
                     max(level) over (partition by elmt) max_lvl,
                     sys_connect_by_path(elmt, '/') || '/' path,
                     sys_connect_by_path(prior bis,'/') wanted_path
               from  relation
               connect by prior elmt = elmt_sup
           ),
     t2 as (
            select  elmt,
                    lvl,
                    max_lvl,
                    path,
                    wanted_path,
                    nvl2(
                         listagg(bis,'/') within group(order by r desc),
                         '/' || listagg(bis,'/') within group(order by r desc) || '/',
                         '/'
                        ) reversed_wanted_path
              from  t1,
                    xmltable(
                             'for $s at $i in ora:tokenize($p,"/")
                                return $s'
                                passing wanted_path as "p"
                                columns
                                  bis varchar2(10) path '.',
                                  r for ordinality
                            )(+)
              where lvl = max_lvl
              group by elmt,
                       lvl,
                       max_lvl,
                       path,
                       wanted_path
           )
select  elmt,
        path,
        substr(
               case mod(lvl,2)
                 when 0 then regexp_replace(replace(wanted_path,'/','|'),'(\|[^|]+)\|([^|]+)','\1/\2')
                 else regexp_replace(wanted_path,'(/[^/]+)/([^/]+)','\1|\2')
               end,
               2
              ) wanted_path,
        substr(
               case mod(lvl,2)
                 when 1 then regexp_replace(replace(reversed_wanted_path,'/','|'),'(\|[^|]*)\|([^|]*)','\1/\2')
                 else regexp_replace(reversed_wanted_path,'(/[^/]*)/([^/]*)','\1|\2')
               end,
               2
              ) reversed_wanted_path
  from  t2
  order by elmt
/

ELMT PATH                           WANTED_PATH          REVERSED_WANTED_PATH
---- ------------------------------ -------------------- --------------------
  10 /10/
  20 /10/20/                        |a                   a|
  30 /10/20/30/                     /a|b                 b/a|
  40 /10/20/40/                     /a|b                 b/a|
  50 /10/20/40/50/                  |a/b|d               d|b/a|
  60 /10/20/60/                     /a|b                 b/a|
  70 /10/70/                        |a                   a|
  80 /10/70/80/                     /a|g                 g/a|
  90 /10/70/90/                     /a|g                 g/a|
 100 /10/100/                       |a                   a|

10 rows selected.

SQL> 


SY.
Re: Hierarchy output [message #621775 is a reply to message #620633] Mon, 18 August 2014 03:46 Go to previous message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Mon, 04 August 2014 22:27

it is bug 7412475, opened on 16-Sep-2008, last updated (or rather seen) on 14-May-2012 but no actual news till now.


I see that the product version and platform has been marked as z*OBSOLETE. Not sure if you originally submitted or was updated later. And yes, there are no updates. I find the desired feature quite useful.
Previous Topic: who can create this output?
Next Topic: Hierarchical Output
Goto Forum:
  


Current Time: Sat Dec 27 07:49:20 CST 2014

Total time taken to generate the page: 0.10431 seconds