Ordering hierarchical queries in Oracle 8i
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Ordering hierarchical queries in Oracle 8i

Hybrid View

  1. #1
    Join Date
    Dec 2001
    Location
    Slovenia, Kranj
    Posts
    82

    Ordering hierarchical queries in Oracle 8i

    How can I order a hierarchical query on Oralce 8i. I know that I can do this on Oracle 9i like this :

    select
    empno, lpad(' ',2*level) || ename as Ename
    from
    emp
    start with
    mgr is null
    connect by prior
    empno = mgr
    order siblings by
    Ename

    and query returns :

    EMPNO ENAME
    ---------- --------------------
    7839 KING
    7698 BLAKE
    7499 ALLEN
    7900 JAMES
    7654 MARTIN
    7844 TURNER
    7521 WARD
    7782 CLARK
    7934 MILLER
    7566 JONES
    7902 FORD
    7369 SMITH
    7788 SCOTT
    7876 ADAMS

    But on Oracle 8i I get :
    ORA-00924: missing BY keyword

    OK, ORDER SIBLINGS BY is not supported on Oracle8i, but I to order hierarchial query on Oracle 8i.

    Thank you in advance

    Aljaz

  2. #2
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    select
    empno, lpad(' ',2*level) || ename as Ename
    from emp
    start with mgr is null
    connect by prior
    empno = mgr
    /

    works fine on 8.1.7

    Cheers.
    It is better to ask and appear ignorant, than to remain silent and remain ignorant.

    Oracle OCP DBA 9i,
    C++, Java developer

  3. #3
    Join Date
    Dec 2001
    Location
    Slovenia, Kranj
    Posts
    82
    works fine on 8.1.7
    Your query works, but you are not using order siblings by

  4. #4
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Hi Aljaz do you get the results wht u want from rotem_fo's query?

    order siblings by this option would not be available in ur release.
    Cheers!
    OraKid.

  5. #5
    Join Date
    Dec 2001
    Location
    Slovenia, Kranj
    Posts
    82
    rotem_fo's query is just fine, but with that query you can not control order of a query.

    Don't worry, I was just wondering I there is a way to control order of hierarhical query with SQL on 8i.

    Thank you all for replays

    Aljaz

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width