Re: ordering within a hierarchy:

From: Frédéric Lefebvre <flefebvre_at_hotmail.com>
Date: Thu, 14 May 1998 13:50:38 GMT
Message-ID: <355af65a.24782715@news.iway.fr>

Hi Glenn,

Try to do somethings like the example behind : the idea is to build a "sorting function" by concatenation of sort keys, padded to max length (in my example DOMAINE).

SQL> create or replace package arbo
  2 as
  3 function tri (p_cod_dom in varchar2) return varchar2;   4 pragma restrict_references (tri, WNDS);   5 end arbo;
  6 /

SQL>
SQL> create or replace package body arbo   2 as
  3 function tri (p_cod_dom in varchar2)   4 return varchar2 is

  5  	 i_ret varchar2 (32000);
  6  	 cursor cur_domaine is
  7  	   select lib_dom from domaine
  8  	   connect by prior dom_appart = cod_dom
  9  	   start with cod_dom = p_cod_dom;
 10    begin
 11  	 for rec_domaine in cur_domaine loop
 12  	   i_ret := rpad (rec_domaine.lib_dom, 30, '@') || i_ret;
 13  	 end loop;
 14  	 --
 15  	 return (i_ret);
 16    exception
 17  	 when others then return (sqlerrm);

 18 end tri;
 19 end arbo;
 20 /

create table DOMAINE
(

	COD_DOM	VARCHAR2(5)	not null,
	DOM_APPART	VARCHAR2(5),
	LIB_DOM	VARCHAR2(30),
	constraint pk_domaine primary key (COD_DOM)

);

create index DOMAINE_FK1 on DOMAINE (DOM_APPART asc);

SQL>
SQL> select lpad (lib_dom, length(lib_dom)+2*level, '.') hierarchie,   2 arbo.tri (cod_dom) tri
  3 from domaine
  4 connect by prior cod_dom = dom_appart   5 start with dom_appart is null
  6 order by tri
  7 /

HIERARCHIE

---

..LITTERATURE
....POESIE
....ROMAN
......AVENTURE ET POLICIER
......DIVERS
..MUSIQUE
....MUSIQUE CLASSIQUE
....MUSIQUE POP, FOLK ET ROCK
..VIE PRATIQUE
....TOURISME ET VOYAGE
Good luck

Fred Received on Thu May 14 1998 - 08:50:38 CDT