Home > SQL Challenge > Recursive Query – Using CTE (Common Table Expression)

Recursive Query – Using CTE (Common Table Expression)

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

image

image

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

image

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

Categories: SQL Challenge
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: