Re: Ordering Hierarchical Queries

On Wed, 25 Mar 1998 21:15:56 +0800, "Junmin Lin" <linjm_at_guomai.sh.cn> 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.
>
>How should I do it?
>
>Thanks in advance.
>
>
>Lin Junmin.
>

Hi Lin,

I had to implement a similar thing myself for several complex reports, i.e. a query output that retains the hierarchical data structure while providing a sort order within each level, and I found that it was a rather tricky thing to accomplish. The solution I implemented was to presort the data using a PL/SQL table in a package:

The before_report trigger calls the initialization procedure in the package, providing the starting record for the hierarchy as a parameter. This recursive procedure will then scan the hierarchy while doing an 'order by order_column' within each level, increment a record counter, and store it in the PL/SQL table indexed by the (numeric) primary key column of my table. In the query itself, I then just call the lookup function of my package in the ORDER BY clause with the pk as a parameter, the function simply retrieves the appropriate counter value from the PL/SQL table. Finally, in the after_report trigger I call the package procedure that resets the counter and deallocates the PL/SQL table.

I don't know if that's the best solution, but it works okay for me; the overhead for this presorting is around 10% to 15% of the overall report execution time for moderate quantities (30000 to 50000 rows) of data in the hierarchical table.

If you're interested in that approach, just send me an email and I'll try to assemble a code example.

Regards,
Peter

-- 
Peter Schneider
peter.schneider_at_okay.net