Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Unanswered: N-Level deep query

    Hi
    I am in the process of creating somewhat like a document tree with N level.
    I want to avoid multiple queries as far as possible, and write a query with show the results in a tree format.

    The following are the entires
    DOCID PARENTID NAME
    2 1 DOC1.2
    3 1 DOC1.3
    4 1 DOC1.4
    5 2 DOC1.2.5
    6 5 DOC1.2.5.6
    7 3 DOC1.3.7

    And I want to display the output in the following fashion
    1
    2 DOC1.2
    5 DOC1.2.5
    6 DOC1.2.5.6
    3 DOC1.3
    7 DOC1.3.7
    4 DOC1.4

    Unions is what I feel might be good for the same, but going to N level deep, I find it slightly scary.

    Anyone has any ideas.
    Thanx and Regards
    Aruneesh

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Thats not a problem with Oracle, use a connect by query which will traverse your tree and display it as a hierarchy (no matter how deep the hierarchy).

    The main problem is you cant do a join in a connect by query but you can get around this by just putting the connect by query in an inline view i.e.

    SELECT *
    FROM
    (
    SELECT rownum idx,LEVEL,LPAD(' ',2*(LEVEL-1)) ||n.DOCID doctree, n.PARENTID , DOCID
    FROM HIERARCHY n
    START WITH n.parentid =1
    CONNECT BY PRIOR n.DOCID = n.parentid
    ) i,
    DOCUMENT d
    WHERE d.DOCID=i.DOCID
    ORDER BY idx asc

    The doctree column will even show the tree with indented nodes.

    If you have 9i there is a function to give you the full path to each node but this isnt available in 8i.

    Alan
    Last edited by AlanP; 07-15-03 at 13:35.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: N-Level deep query

    Originally posted by aruneeshsalhotr
    Hi
    I am in the process of creating somewhat like a document tree with N level.
    I want to avoid multiple queries as far as possible, and write a query with show the results in a tree format.

    The following are the entires
    DOCID PARENTID NAME
    2 1 DOC1.2
    3 1 DOC1.3
    4 1 DOC1.4
    5 2 DOC1.2.5
    6 5 DOC1.2.5.6
    7 3 DOC1.3.7

    And I want to display the output in the following fashion
    1
    2 DOC1.2
    5 DOC1.2.5
    6 DOC1.2.5.6
    3 DOC1.3
    7 DOC1.3.7
    4 DOC1.4

    Unions is what I feel might be good for the same, but going to N level deep, I find it slightly scary.

    Anyone has any ideas.
    Thanx and Regards
    Aruneesh
    Try using CONNECT BY:

    select rpad( ' ', 2*level, ' ' ) || docid || name
    from tab
    connect by prior docid = parentid
    start with docid = 1;

    This requires an extra row in the table:
    DOCID PARENTID NAME
    1 DOC1

  4. #4
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow It works fine

    Andrew,
    Thanx a ton for getting the solution to the recursive problem posted promptly. That would surely save me much time, after last nights immediate spec change.
    Thanx and Regards
    Aruneesh

Posting Permissions

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