Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2012
    Posts
    1

    Unanswered: SQL Recursion Parent/Child

    I am new to IBM DB2 (1 month)

    Here is my situation:

    I have the following query:

    SELECT nmnm.MSMS_PTPTP_NONO_ID, MSMS_PTPTP_NM, PRPR_PTPTP_NONO_ID
    FROM PRTPR.N_MSMS_PRPR_TXTX nmnm
    LEFT JOIN PRTPR.MSMS_PRPR_TXTX ON nmnm.MSMS_PTXPT_NONO_ID = PRTPR.MSMS_PRPR_TXT.MSMS_PTXPT_NONO_ID

    1002|ccccccc|1001

    1001|dddddddd|x2218

    PRNT_PTXMY_NODE_ID it the parent and needs to look for the value in MSTR_PTXMY_NODE_ID and that value PRNT_PTXMY_NODE_ID needs to look in MSTR_PTXMY_NODE_ID -- Recursion

    Example:
    \pppppp\dddddddd\ccccccc\pp - etc...

    I tried using the CONNECT_BY_ROOT but was unsuccessful


    Any help would be gratefully appreciated.

    David

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    is there a problem with CONNECT_BY_ROOT or with your query you don't display
    os ?? db2level ?? db2 platform??
    try to read the entry in this forum : how to submit a question ....and what todo before submitting a question....
    finally check the doc .. there must be some samples..
    search in this forum.. the sql special-x : Mr Tonkuma submitted many samples on this..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by dlg63 View Post
    I tried using the CONNECT_BY_ROOT but was unsuccessful
    CONNECT_BY_ROOT is an Oracle nonstandard SQL extension; DB2 uses the standard RECURSIVE construct with a common table expression; search for "recursive" in earlier threads in this forum for examples, or have a look in the DB2 SQL Reference Guide (e.g. in Chapter 5, Select Statement, Common Table Expression, Recursion Example: bill of materials).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Peter.Vanroose View Post
    CONNECT_BY_ROOT is an Oracle nonstandard SQL extension;
    Since version 9.7 DB2 LUW has an Oracle compatibility mode, whereas it supports or tolerates a number of Oracle proprietary SQL constructs, including the CONNECT BY syntax for recursive queries and the CONNECT_BY_ROOT function.
    ---
    "It does not work" is not a valid problem statement.

Tags for this Thread

Posting Permissions

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