Re: Ordering Hierarchical Queries

Junmin Lin wrote:
>
> Hi,
>
> I have a question about hierarchical queries.
>
> Suppose I have a table:
>
> PARENT NOT NULL VARCHAR2(10)
> CHILD NOT NULL VARCHAR2(10)
> SORT_ORDER NUMBER(5)
>
> I want to do a hierachical query, I could do:
>
> select parent, child, sort_order, level
> from relation
> connect by parent = prior child start with parent = 'A' ;
>
> But, if I want the out put sorted via sort_order and at the same time
> preseved the level.
> Lin Junmin.

I used a temporary table to store the result of the query to conserve both the level and the order

delete from tree;
commit;

insert into tree (seqnr,levl,child,parent)   select rownum,level,child,upper(kode)
    from your_table
    start with parent=upper(kode)
    connect by prior child=parent;
commit;

kode is the parameter (parent) to start the procedure wirth

-- 
******************************************************************
****************** A L C A T E L  T E L E C O M ******************
Herman Viaene
Berkenrodelei 33, B-2660 Hoboken, Belgium
Division: Radio Space and Defence
Tel: (32/3)8295591
Fax: (32/3)8295502
OIS-mail: viaeneh_at_AM@BTMA97
Internet-mail: viaeneh_at_btmaa.bel.alcatel.be
X400: Viaene Herman
(C=BE,A=RTT,P=ALCANET,OU1=BELA1,O=ALCATEL,S="Viaene",G="Herman")
******************************************************************