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

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..

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).

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.