Re: Order in a hierarchical select

Hi Dan,

Try to use index hint inside the select statement. Something like this:

  select /*+ index(your_table your_index) */ lpad(...

And think about only one (maybe dummy) root in your "tree" for the best result.

Hope that helps.

DanHW wrote:
>
> >Problem description:
> >
> >I want to have an order by name within a hierarchical select:
> >SELECT -1, Level, Name, null, to_char(ID)
> > FROM TABLE
> > CONNECT BY PRIOR ID=CHILD_ID
> > START WITH ID IN
> > (SELECT ID
> > FROM TABLE
> > WHERE CHILD_ID IS NULL);
> >
> >
> >The original table content is:
> >
> >ID Name Child_ID
> >-----------------------
> >1 a 3
> >2 b 4
> >3 d null
> >4 c null
> >5 f 4
> >6 e 4
> >
> >The hierarchical select result should look like:
> >
> >c
> > |-b
> > |-e
> > |-f
> >d
> > |-a
> >
>
> That's correct - can't do a ORDER BY in a CONNECT BY...
>
> I had a similar situation; the only way I could get the desired results
> was to add a new column which was the concatenation of the parents (the
> part you want to see/order by), populate it through triggers (statement,
> not row triggers) that programatically walked the tree. Then I could
> sort on this column, without needing to do the tree-walk. I think I also
> kept the number of levels.
>
> Let us know if you find another way...
>
> Dan Hekimian-WIlliams

-- 
Valeri Sorokine
Oracle Certified Application Developer, Rel.2
ProSoft, Russia, Moscow, Information Systems Division
Phone: +7 (095) 234 0636 ; FAX: +7 (095) 234 0640
E-mail: vsorokin_at_dd.ru   ; http://www.dd.ru