Recursive Query – Using CTE (Common Table Expression)

Here is a typical table that is designed to be recursive.

Using CTE, we can get all the child records, direct or indirect, that has a parent ID.

WITH SOME_HIERARCHY (SOME_ID, SOM_DEPT_ID, SOME_NM, SOME_DESC, PARENT_SOME_ID)
         AS   
         (    SELECT    SOME_ID, SOM_DEPT_ID, SOME_NM, SOME_DESC, PARENT_SOME_ID
            FROM    DB2SCHEMA.T_SOME_DEF  
            WHERE    PARENT_SOME_ID IN ( 384394939)
            UNION ALL
            SELECT    A.SOME_ID, A.SOM_DEPT_ID, A.SOME_NM, A.SOME_DESC, A.PARENT_SOME_ID
            FROM    DB2SCHEMA.T_SOME_DEF  AS A, SOME_HIERARCHY AS B
            WHERE    A.PARENT_SOME_ID = B.SOME_ID
         )
         SELECT * FROM SOME_HIERARCHY ORDER BY PARENT_SOME_ID, SOME_NM