Re: Order in a hierarchical select

From: Craig Ledbetter <craigl_at_gte.net>
Date: Sat, 18 Dec 1999 10:22:14 GMT
Message-ID: <qfJ64.262$_f.762_at_dfiatx1-snr1.gtei.net>


Karoly,
[Quoted] Here's another way. Your basic problem is that you want the hierarcical [Quoted] output, but you want it ordered first by the parent record name, then by the [Quoted] child record name. You just make a couple of sort fields in the hierarchical [Quoted] query, then use it as an in line subquery in the FROM clause so that you can [Quoted] ORDER BY the sort fields afterwards (can't use ORDER BY in view accessed [Quoted] with CONNECT BY). Using "PRIOR Name" gets you the parent name, if there is [Quoted] 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 [Quoted] 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. [Quoted] There are probably other ways to do this, but the concept is the same.

Here is the spool file:

CREATE TABLE TEST_TABLE

  (ID       NUMBER(1),
   NAME     CHAR(1),

   CHILD_ID NUMBER(1)
   );
INSERT INTO TEST_TABLE VALUES
   (1,'a', 3);
INSERT INTO TEST_TABLE VALUES
   (2,'b', 4);
INSERT INTO TEST_TABLE VALUES
   (3,'d', NULL);
INSERT INTO TEST_TABLE VALUES
   (4,'c', NULL);
INSERT INTO TEST_TABLE VALUES
   (5,'f', 4);
INSERT INTO TEST_TABLE VALUES
   (6,'e', 4);

SELECT
    RESULT
FROM
   (SELECT

[Quoted]         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
>>
Received on Sat Dec 18 1999 - 11:22:14 CET

Original text of this message