Is Recursive Retrieval Possible using SQL or Procedures
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Is Recursive Retrieval Possible using SQL or Procedures

  1. #1
    Join Date
    Dec 2000
    Posts
    255
    Hi

    I have a Table with following columns

    create table topology(
    nid number(19) primary key,
    ipaddress varchar2(15),
    macaddress varchar2(15),
    status varchar2(10),
    parentid number(19) references topology(nid));

    There is a self reference in the table.
    I have Stored Data in a topological tree structure.
    There is a single record at Top Level for which the parent id is null . Call it as owner.
    This Owner has 2 children (Thats the parent id of this is nid of owner) Now these 2 children have 3 to 4 children each and further those children can have further.
    The level of tree structure can be much bigger and its dynamic. There can be records adding in between tree strucure.
    My Problem is I want a query or function which will return the sequential tree structure of an
    element which I input. If I input a owner's id then it should retrieve all the hierarchy of children below that element and to whatever level it goes.

    Amol

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    you should use something like (level, connect by, prior and start with are reserved keywords) :

    SELECT level,parentid
    FROM topology
    start with parentid is null
    CONNECT BY parentid = PRIOR nid

  3. #3
    Join Date
    Oct 2001
    Posts
    1
    I have similar type of question but its different. Is there any way to get the top level node using the intermediate child level node?

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Pipo wrote to you RIGHT statment, because he changed direction in CONNECT BY .. PRIOR :

    from child --> parent
    to parent --> child

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