Re: Connect by prior sorts incorrectly

The reason is that the connect by is in an inline view. It is executed and generates a result set that is then used to join with the other tables in your from clause and this is there is no guarantee that the order will not be changed when you do this. If you want your data to retain the hierarchical order you will need to perform the select differently. I would recomend breaking it up into two separate cursors in a pl/sql script.

Hope this helps,
Patrick Flahan
flahan_at_earthlink.net

DECLARE
  CURSOR curHierarchy
   IS
   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;

  CURSOR curDetails(pOr_Trigram <Type>)    IS
SELECT org.or_name, rr.re_year, rr.re_period, op.op_id, op.sy_id FROM reop_orgpersp op, rr_remember rr
WHERE pOr_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=pOr_Trigram
)
AND op.re_id=rr.rr_id
AND op.pe_name=my_pe_name;

BEGIN
......

Mathias Nilsson <mathias.nilsson_at_NOSPAMeng.ericsson.se> wrote in message news:36E921C1.E9212E99_at_NOSPAMeng.ericsson.se...
>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 - 09:53:07 CST