Re: Connect by prior sorts incorrectly

From: <mark_fredericks_at_my-dejanews.com>
Date: Sat, 13 Mar 1999 06:24:46 GMT
Message-ID: <7cd0ba$t2o$1@nnrp1.dejanews.com>

Other reply posting have answered the why. Here may be a fix.

I can not test this from home, but I think this will work.

Try this version of your query to restore the desired order. It keeps the connect by order information from the nested query, to use to order the main join query.

HTH MARK

---

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,

        ROWNUM hierarchy_order
 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
ORDER BY org.hierarchy_order
;

---

In article <36E921C1.E9212E99_at_NOSPAMeng.ericsson.se>,   Mathias Nilsson <mathias.nilsson_at_NOSPAMeng.ericsson.se> wrote:
> Why does my hierarchical query return the highest level last? (I'm using

>.....

> 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;

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Sat Mar 13 1999 - 00:24:46 CST