Re: Connect by prior sorts incorrectly

The order of the start connect by may be lost because of the join to rr and op. Try to use a nested joins by using hints. You may need to add indexes for to do this.

To confirm, use explain to reveal all.

This raises an interesting point : If order by is the only way to guarantee the order of the result set how do you use order by to return the result set in the order of a start connect by, as in this case.

Mathias Nilsson wrote:

> Why does my hierarchical query return the highest level last? (I'm using
> Oracle 7.3.4) This is what my select looks like:
>
> SELECT org.or_name, rr.re_year, rr.re_period, op.op_id, op.sy_id
> FROM
> (
> SELECT or_trigram, LPAD(' ',2*(LEVEL-1)) || or_name or_name
> FROM or_organization
> START WITH or_trigram = my_or_trigram
> CONNECT BY PRIOR or_trigram = or_parent
> ) org, reop_orgpersp op, rr_remember rr
> WHERE org.or_trigram=rr.or_trigram
> AND (rr.re_year*100)+rr.re_period=
> (
> SELECT MAX((rrx.re_year*100)+rrx.re_period)
> FROM rr_remember rrx
> WHERE rrx.or_trigram=org.or_trigram
> )
> AND op.re_id=rr.rr_id
> AND op.pe_name=my_pe_name;
>
> I have two in parameters, my_or_trigram (organization id) and
> my_pe_name.
>
> Table or_organization:
> ID Name Parent
> A Top Organisation <1st level> null
> B Department A <2nd level> A
> C Department B <2nd level> A
> D Department C <2nd level> A
> E Project X <3rd level> C
>
> When I do a drilldown from Top Organisation, the data looks like this:
> Department A <2nd level>
> Department B <2nd level>
> Project X <3rd level>
> Top Organisation <1st level>
>
> Why does Top Organisation end up last in my recordset? When I run the
> subselect
> on its own, the organizations appear in the correct order.
>
> Thanks in advance
> Mathias
Received on Fri Mar 12 1999 - 11:05:03 CST