Re: Sorting hierarchical query

From: John P. Higgins <jh33378nospam_at_deere.com>
Date: 1997/11/08
Message-ID: <34648462.7F89@deere.com>

This is a multi-part message in MIME format.

--------------74FA6D83AD3
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Oren Nakdimon wrote:

> 
> Hi.
> Consider the employees table, where each record contains the id number
> and the name of an
> employee, and, in addition, the id number of the employee's manager:
> 
>   create table EMP (
>     EMPNO number(4),
>     ENAME varchar2(100),
>     MGR number(4) );
> 
> I select the employees' tree by:
> 
>   select EMPNO,ENAME
>   from EMP
>   connect by prior EMPNO = MGR;
> 
> (never mind the tree-shape displaying now).
> 
> Now, I need to improve this query so all the employees with the same
> manager will be ordered by
> ENAME.
> Does anyone has an idea how to do that ?
> (The SELECT must be in SQL. However, using PL/SQL functions within the
> query is allowed).
> 
> Thanks.
> --
> ====================================================
> Oren Nakdimon
> Golden Screens Ltd.
> address: 17 Abba-Hillel st., Ramat-Gan 52522, ISRAEL
> email:   oren_at_gsit.co.il
> tel:     +972-3-7510836
> fax:     +972-3-7518221
> ====================================================

--------------74FA6D83AD3
Content-Type: text/html; charset=us-ascii; name="cod06106.html"
Content-Transfer-Encoding: 7bit

Content-Disposition: inline; filename="cod06106.html" Content-Base: "file:///C|/WINDOWS/TEMP/cod06106.html"

<BASE HREF="file:///C|/WINDOWS/TEMP/cod06106.html">

<BASE HREF="http://www.oramag.com/code/cod06106.html">

<html>
<head>
<TITLE> Code Depot: Sorting and Ordering Hierarchical Data</TITLE>
</head>

<CENTER>
<BODY BGCOLOR="#ffffff" TEXT="#000000" LINK="#990000" ALINK="#550000" VLINK="#990000">
<A HREF="../map/menuimgs.map"><IMG BORDER=0 SRC="../images/forumhed.jpg" ISMAP ALT="The Forum"></A>
</center>


CODE DEPOT 

JUNE 10, 1996



<h1> Sorting and Ordering Hierarchical Data</h1>
This "Code Depot" entry comes from Oracle Magazine Interactive user Janco Tanis, a Software Engineer/Analyst at COAS in The Netherlands. You may e-mail Janco at <A HREF="mailto:Janco.Tanis_at_unilever.com">Janco.Tanis_at_unilever.com</A>. Janco's Oracle WebMaster t-shirt is on its way to him!

With Oracle6, the documentation notes that you cannot use the ORDER BY clause to sort a hierarchical query. However, in Oracle7 Release 7.1, you can create user-defined stored procedures to solve this problem and maintain the correct hierarchy for your data when you need it. This example uses geography of regions and countries to show you how this might come in handy. The Oracle SQL extension CONNECT BY PRIOR/START WITH will return rows in a hierarchical order. But (there is always a but) if you use the ORDER BY clause in a hierarchical query, Oracle orders rows by the ORDER BY clause rather than in the order per sublevel in your hierarchy.

An Example Without Using ORDER BY

<pre><code> SELECT RPAD( ' ', LEVEL * 5 ) || Name FROM Universe

      CONNECT BY PRIOR Name = Parent START WITH Parent IS NULL; 
      
     World 
        Europe 
                England 
                Germany 
                The Netherlands 
        Asia 
                Japan 
                China 
        America 
                United States 
                Mexico 
        Africa 
                Egypt 
                Morocco

</pre></code>


Example with ORDER BY

Using an ORDER BY on the displayed column will order the names alphabetically but it will disturb the hierarchy. 

<pre><code> SELECT RPAD( ' ', LEVEL * 5 ) || Name FROM Universe

      CONNECT BY PRIOR Name = Parent START WITH Parent IS NULL  
      ORDER BY Name; 
      
        Africa 
        America 
        Asia 
                China 
                Egypt 
                England 
        Europe 
                Germany 
                Japan 
                 Mexico 
                Morocco 
                The Netherlands 
                United States 
     World

</pre></code>


<h3>MAINTAINING THE HIERARCHY</H3>

By making use of Oracle7 Release 7.1 functionality, you can solve this problem. Release 7.1 allows the developer to make use of user-defined stored procedures in SELECT statements. You can use this functionality to create a string . For example, for England the string should be 'World/Europe/England/'. If you base the order on this string, you will get the correct result.

 Example With User-Defined Function in the ORDER BY Clause

<pre><code> SELECT SUBSTR( RPAD( ' ', LEVEL * 5) || Name, 1, 40)

      FROM Universe 
      CONNECT BY PRIOR Name = Parent 
      START WITH Parent IS NULL 
      ORDER BY UniverseSortOrder( Name ) 
     ; 
      
     World 
        Africa 
                Egypt 
                Morocco 
        America 
                Mexico 
                United States 
        Asia 
                China 
                Japan 
        Europe 
                England 
                Germany 
                The Netherlands

</pre></code>


This will give the correct result and order all items as they belong within the geographical hierarchy.

<h3>DEMONSTRATION SCRIPT</h3>

The demonstration script below shows how the technique creating the user-defined procedure works. 

<pre><code>

     REM Create hierarchy table 
     CREATE TABLE Universe( 
      Parent VARCHAR2(30) REFERENCES Universe,  
      Name  VARCHAR2(30) PRIMARY KEY 
     ); 
      
     REM Some test data 
     INSERT INTO Universe VALUES ( NULL, 'World' ) ;  
     INSERT INTO Universe VALUES ( 'World', 'Europe' ) ;  
     INSERT INTO Universe VALUES ( 'Europe', 'England' ) ; 
     INSERT INTO Universe VALUES ( 'Europe', 'The Netherlands' ) ;  
     INSERT INTO Universe VALUES ( 'Europe', 'Germany' ) ;  
     INSERT INTO Universe VALUES ( 'World', 'Asia' ) ;  
     INSERT INTO Universe VALUES ( 'Asia', 'Japan' ) ;  
     INSERT INTO Universe VALUES ( 'Asia', 'China' ) ;  
     INSERT INTO Universe VALUES ( 'World', 'America' ) ; 
     INSERT INTO Universe VALUES ( 'America', 'United States' ) ;  
     INSERT INTO Universe VALUES ( 'America', 'Mexico' ) ; 
     INSERT INTO Universe VALUES ( 'World', 'Africa' ) ;  
     INSERT INTO Universe VALUES ( 'Africa', 'Egypt' ) ;  
     INSERT INTO Universe VALUES ( 'Africa', 'Morocco' ) ; 
      
     REM Create a sort function for the Universe 
     CREATE OR REPLACE FUNCTION UniverseSortOrder( PKey Universe.Name%TYPE )  
     RETURN VARCHAR2 IS 
        Path VARCHAR2(2000); 
     BEGIN 
        Path := PKey; 
        -- Insert all previous parent records like a directory structure 
        -- e.g. World/Europe/... 
        FOR cRec IN ( SELECT Parent FROM Universe  
                       CONNECT BY PRIOR Parent = Name 
                       START WITH Name = PKey ) LOOP 
           Path := cRec.Parent || '/' || Path; 
        END LOOP; 
        RETURN Path; 
     END; 
     / 
      
     REM And try it out... 
     SELECT SUBSTR( RPAD( ' ', LEVEL * 5) || Name, 1, 40) "The Universe" 
      FROM Universe 
      CONNECT BY PRIOR Name = Parent 
      START WITH Parent IS NULL 
      ORDER BY UniverseSortOrder( Name ) 
     ;

</pre></code>


<center>Return to the Depot
<HR NOSHADE>


All specific technical questions from users of Oracle supported products should be directed to Oracle's Technical Support Department. Oracle does not provide any warranty as to the accuracy of any information provided through Oracle Magazine Interactive. Oracle shall not be liable for any damages incurred as a result of reliance on any information provided herein.

Copyright © 1994, 1995, 1996 & 1997 Oracle Corporation. All Rights Reserved.
 

<center>
<A HREF="../index.html">Home!</A> |
<A HREF="../html/listing.html">Columns</A> |
<A HREF="../archives/archives.html">Archives</A> |
<A HREF="../html/forum.html">Forum</A> |
<A HREF="../html/services.html">Services</A> |
<A HREF="../html/subcard.html">Subscribe!</A> |
<A HREF="../archives/search.html">Search?</A>
</body>
</html>

--------------74FA6D83AD3-- Received on Sat Nov 08 1997 - 00:00:00 CST