Re: Order in a hierarchical select

Craig

As far as I understand your select-statement, it works only for two-level-trees; for the third level you had to insert a third sort-field.
I personally am disappointed in the hierarchical query - too many restrictions (no joins, no use of views, no hierarchical order by). I either used some help-field or temporary data filled with procedural logic as suggested by the former reply, or a recursive function that delivers the concatenated name of the parents about like in your solution, but extended for arbitrary depths (or almost):

create or replace function emp_mgr_names (pEmpno in number)
return varchar2
-- return concatenated string of emp's mgr's names and his own name is
  Cursor curEmp (pEmpno in number) is
  select ename, mgr
  from scott.emp
  where empno = pEmpno ;

  recEmp curEmp%rowtype ;

  wResult Varchar2(2000) ;
  /* why not ? - runtime considerations see below */   /* max levels is lenght of wResult divided by lenght of names */

begin
  open curEmp(pEmpno) ;
  fetch curEmp into recEmp ;
  close curEmp ;

  if recEmp.mgr is not null then

• we are not the boss. Put our
• mgr's name(s) in front of our
• own name wResult := emp_mgr_names(recEmp.mgr) ; end if ;
  • finally, add our own name (to an eventually null wResult) wResult := wResult || recEmp.ename ;

  return (wResult) ;
end ;

Now, you can use this string as order-by-field:

select empno, ename, emp_mgr_names(empno) sort_mgr from scott.emp
order by sort_mgr

gives:

7839	King      	King
7698	Blake     	King      Blake
7499	Allen     	King      Blake     Allen
7900	James     	King      Blake     James
7654	Martin    	King      Blake     Martin
7844	Turner    	King      Blake     Turner
7521	Ward      	King      Blake     Ward
7782	Clark     	King      Clark
7934	Miller    	King      Clark     Miller
7566	Jones     	King      Jones
7902	Ford      	King      Jones     Ford
7369	Smith     	King      Jones     Ford      Smith
7788	Scott     	King      Jones     Scott
7876	Adams     	King      Jones     Scott     Adams

(This example works only because the names in scott.emp are right-padded with blanks, which is rather unusual. With real-life-data, you had to right-padd them yourself in the function).

I am quite sure there is no non-procedural-solution, as you can't use the result of a function in a prior-clause (and thus can't force the recursive technique into the select-statement).

However, there is a drawback to this solution: the cursor in the function is opened 39 times for the query above (once for every delivered name), which is quite a lot. And it get's worse very fast, if the result-set get's bigger. This is because we visit every node in the tree and parse the tree towards it's root once for every node. The Mgr's of Jones for example are calculated many times, once for every of it's subordinates and once for their subordinates... A much better technique is to write a depth-first-algorithm starting at the root of the tree, but in this case you need to write some temporary data.

Regards
Pascal

In article <qfJ64.262$_f.762_at_dfiatx1-snr1.gtei.net>,   "Craig Ledbetter" <craigl_at_gte.net> wrote:
> Karoly,
> Here's another way. Your basic problem is that you want the
hierarcical
> output, but you want it ordered first by the parent record name, then
by the
> child record name. You just make a couple of sort fields in the
hierarchical
> query, then use it as an in line subquery in the FROM clause so that
you can
> ORDER BY the sort fields afterwards (can't use ORDER BY in view
accessed
> with CONNECT BY). Using "PRIOR Name" gets you the parent name, if
there is
> one. If there is no parent (ie, Child_id is null) then you have to be
> careful that you default a value into the second sort field so that
it will
> sort before any record that has a child. To do this I converted your
> character name to ascii, and defaulted to zero when there was no
parent.
> There are probably other ways to do this, but the concept is the same.
>
> Here is the spool file:
>
...
>
> SELECT
> RESULT
> FROM
> (SELECT
> DECODE(CHILD_ID,
> NULL, NAME,
> LPAD('|', (LEVEL -1)*2, ' ')||'-'||NAME
> ) RESULT,
> NVL(PRIOR NAME, NAME) FIRST_SORT,
> DECODE(CHILD_ID,
> NULL, 0,
> ASCII(NAME)
> ) SEC_SORT
> FROM
> TEST_TABLE
> CONNECT BY PRIOR ID = CHILD_ID
> START WITH CHILD_ID IS NULL
> )
> ORDER BY
> FIRST_SORT,
> SEC_SORT;
>
> RESULT
> ------------------------------
> c
> |-b
> |-e
> |-f
> d
> |-a
>
> Which is just what you wanted.
> Regards,
> Craig
>
> >>Problem description:
> >>
> >>I want to have an order by name within a hierarchical select:
> >>SELECT -1, Level, Name, null, to_char(ID)
> >> FROM TABLE
> >> CONNECT BY PRIOR ID=CHILD_ID
> >> START WITH ID IN
> >> (SELECT ID
> >> FROM TABLE
> >> WHERE CHILD_ID IS NULL);
> >>
> >>
> >>The original table content is:
> >>
> >>ID Name Child_ID
> >>-----------------------
> >>1 a 3
> >>2 b 4
> >>3 d null
> >>4 c null
> >>5 f 4
> >>6 e 4
> >>
> >>The hierarchical select result should look like:
> >>
> >>c
> >> |-b
> >> |-e
> >> |-f
> >>d
> >> |-a
> >>
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Dec 18 1999 - 22:38:18 CET