Re: HOWTO ORDER HIERARCHICAL QUERIES ?

Jan Rosinowski wrote:
>
> >order by level,org_chart
>
> doesn't work as the author correctly stated.

I received a private email with the solution, but I think the answer is of public interest. Thanks Mark.

You have to use hint.

create index i_emp_mgr_ename on emp(mgr,ename);

SELECT /*+ INDEX(emp i_emp_mgr_ename) */ --this hint enforces fetching rows by index from table.
LPAD(' ',2*(LEVEL-1)) || ename org_chart,

        empno, mgr, job
    FROM emp
    START WITH job = 'PRESIDENT'
    CONNECT BY PRIOR empno = mgr;

Mark

In reality my problem is a bit more complex, because the user can choose between two sort criteria. I used two indexes and dynamic pl/sql to execute the statement.

Thanks All

Kai
--
$f

Unix, WinNT and MS-DOS. The Good, The Bad and The Ugly. Kai Poitschke MailTo:kai.poitschke[at]computer.org Date/Time: $d/$t Received on Mon Sep 13 1999 - 16:14:43 CDT