Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2007
    Posts
    38

    Unanswered: Alternate for recursive sql

    Hi all,
    I am presently using Recursvive sql to solve hierarchy problem, is there any option other than recursive sql that you are aware of. If so can you please help me out here

    Thank you all in advance.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think that recursive query is a simple, straightforward, and general way to solve the hierarchy problems.
    (Other persons may have another opinions.)

    AFAIK, you can use self outer joins for each level of hierarchy, if the expected upper limit of level of hierarchy was fixed(or known).
    Another way would be using multiple nested table expressions. But, it would be essentially equivalent to using self outer joins.
    I remember the way of IMS(hierarchical DBMS)
    which is to define logical DBD to connect parent and child then to define PCB including up to 15 levels of descendant,
    or the way of Network DBMS which need to navigate hierarchy in applications.

    By the way, you miay know that DB2 V9.5 supports CONNECT BY syntax for recursive query by setting the DB2 registry variable, additional to CTE syntax.
    I don't know the detail of the syntax, because I only saw the Serge Rielau's article briefly.
    DB2 Viper 2 compatibility features
    http://www.ibm.com/developerworks/db...dm-0707rielau/
    Last edited by tonkuma; 06-22-08 at 15:56.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Another option besides recursive SQL would be to use SQL control statements, for example LOOP ... END LOOP and then put your processing logic inside the loop.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Nov 2007
    Posts
    38
    Thank you so much guys

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    another option is joe celko's nested set model

    you need to replace parent_id with lft and rgt columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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