Re: HELP sorting a table before hierarchical query

From: Marc <>
Date: Wed, 25 Aug 1999 07:22:39 +0200
Message-ID: <7pvun8$cvc$>

If you use an ORDER BY, you will of course loose the hierarchy. It might work by forcing Oracle to use an index.


create index concept_sort on tables.concept_hierarchy(parent_concept, current_concept) -- order of columns depends on what you want /
select /*+ INDEX(tables.concept_hierarchy concept_sort) */

   lpad('&nbsp',5*(level-1))||current_concept,parent_concept from tables.concept_hierarchy
start with parent_concept='Concepts'
connect by prior current_concept=parent_concept;


BPoulin wrote in message <7pujhk$hnu$>...
>Is there any way I can sort a table (short of completely recreating
>it,unless there is a -good- way of automating that) so that a hierarchical
>query will retrieve the information in alphabetical order (within each
>subgroup of the hierarchy)?
>The query will be
>select lpad('&nbsp',5*(level-1))||current_concept,parent_concept
>from tables.concept_hierarchy
>start with parent_concept='Concepts'
>connect by prior current_concept=parent_concept;
>This worked fine and was easy to read when I created the table using
>... as select ... order by' but after any insertions into the structure the
>ordering is lost. I've tried to copy all the records to another table,
>sorting them as I go, and then copying them back(again with 'order by') but
>the order is still lost. I've tried this in SQL*PLUS and with a cursor in a
>procedure. I've also tried using a view to sort the records before doing
>hierarchical query but this is not supported.
>The readability of the results of this query is crucial so I have to do
>somehow. I'm hoping I'm not going to have to recreate the table every time
>insert something. This will be output to a web page.
>Thanks in advance.
Received on Wed Aug 25 1999 - 07:22:39 CEST

Original text of this message