Re: Ordering Hierarchical Queries

> > 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
No temporary table is needed. Just create a couple of indexes:

	create index idx1 on relation(child,sort_order);
	create index idx2 on relation(parent,sort_order);

Then, try the query and see what happens.

Sergei

VIAENEH wrote:
>
> 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_at_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")
> ******************************************************************
Received on Thu Mar 26 1998 - 00:00:00 CET