Re: Order in a hierarchical select
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