CODEWITHSUNDEEP
sqlrecursiondb2common-table-expression
Cal Harris
Cal Harris

Reputation: 67

DB2 CTE Recursion on one table, 6 levels

I have to write a recursion on a db2 table that contains a parent/child relationship in its column values. it's a terrible design but unfortunately I have no control over that. It's a data dump from SalesForce.

Each record in the dump table represents a level of product code groupings. Starting from the top level Indutstrial, AG, OIL, etc, each group has subgroups with group codes until reaching the bottom level that contains the actual product code. Given the product code (STCC_RECORD_ID) I need to retrieve the top parent's GROUP_CODE. I am stuck, any insight would be greatly appreciated. Here is my latest version of the SQL:

WITH X(ID,STCC_RECORD_ID,PARENT_ID,GROUP_CODE,SEG_NAME) AS 
(
SELECT P.ID,P.STCC_RECORD_ID,P.PARENT_ID,P.GROUP_CODE,P.SEG_NAME
        FROM HB_EXTRACT.SEGMENTATION P
        WHERE STCC_RECORD_ID='0113211' 
UNION ALL
SELECT ID,STCC_RECORD_ID,PARENT_ID,GROUP_CODE,SEG_NAME
        FROM x
        WHERE x.PARENT_ID = ID
) 
SELECT * FROM X

Here is the output from that. Only the bottom record shows: enter image description here

This is the desired output: enter image description here

Upvotes: 0

Views: 815

Answers (1)

data_henrik
data_henrik

Reputation: 17118

As I understand, you have to use a bottom-up approach.

WITH X(ID,STCC_RECORD_ID,PARENT_ID,GROUP_CODE,SEG_NAME, Level) AS 
(
SELECT P.ID,P.STCC_RECORD_ID,P.PARENT_ID,P.GROUP_CODE,P.SEG_NAME, 0
        FROM HB_EXTRACT.SEGMENTATION P
        WHERE STCC_RECORD_ID='0113211' 
UNION ALL
SELECT P.ID,P.STCC_RECORD_ID,P.PARENT_ID,P.GROUP_CODE,P.SEG_NAME, Level+1
        FROM x, HB_EXTRACT.SEGMENTATION P
        WHERE x.STCC_RECORD_ID=P.PARENT_ID
        AND Level<7
) 
SELECT * FROM X
WHERE Level>4

In each iteration, you join the next level (parent) based on the current record. The level column is used to later on select a specific layer of table X.

Upvotes: 2

Related Questions