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:
This is the desired output:
sql recursion db2 common-table-expression
asked Nov 20 '18 at 18:26
Cal HarrisCal Harris
307
add a comment |
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:
This is the desired output:
sql recursion db2 common-table-expression
asked Nov 20 '18 at 18:26
Cal HarrisCal Harris
307
add a comment |
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:
This is the desired output:
sql recursion db2 common-table-expression
asked Nov 20 '18 at 18:26
Cal HarrisCal Harris
307
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:
This is the desired output:
sql recursion db2 common-table-expression
sql recursion db2 common-table-expression
asked Nov 20 '18 at 18:26
Cal HarrisCal Harris
307
asked Nov 20 '18 at 18:26
Cal HarrisCal Harris
307
asked Nov 20 '18 at 18:26
Cal HarrisCal Harris
307
asked Nov 20 '18 at 18:26
Cal HarrisCal Harris
307
asked Nov 20 '18 at 18:26
Cal HarrisCal Harris
307
307
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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.
answered Nov 20 '18 at 18:52
data_henrikdata_henrik
9,28421031
Why the level restrictions? The OP can't know, ahead of time, how deep the tree is. Oh, and please, don't use the comma-separatedFROMclause - just explicitly list out theJOIN.
– Clockwork-Muse
Nov 20 '18 at 19:13
It says 6 levels and it is a precaution for testing.
– data_henrik
Nov 20 '18 at 19:25
Thanks, that helped with a couple of things I was missing like level... but it's still just returning the bottom (top?) record, same as before. It's almost like it's not matching the parent_id, but when I look at it manually they definitely match. Or, it's not doing the recursion. I'll keep at it, I appreciate your help.
– Cal Harris
Nov 20 '18 at 19:29
@Clockwork-Muse when I try to use any type of Join it errors and says 'an explicit join including an ON clause is not allowed in the fullselect of the recursive operation'
– Cal Harris
Nov 20 '18 at 19:36
Update: My apologies, your solution worked fine. We had the parent_id trying to match the STCC_RECORD_ID instead of just ID. It's works like a champ, you're my hero. Thanks!
– Cal Harris
Nov 20 '18 at 19:40
|
show 1 more comment
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53399266%2fdb2-cte-recursion-on-one-table-6-levels%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Nov 20 '18 at 18:52
data_henrikdata_henrik
9,28421031
Why the level restrictions? The OP can't know, ahead of time, how deep the tree is. Oh, and please, don't use the comma-separatedFROMclause - just explicitly list out theJOIN.
– Clockwork-Muse
Nov 20 '18 at 19:13
It says 6 levels and it is a precaution for testing.
– data_henrik
Nov 20 '18 at 19:25
Thanks, that helped with a couple of things I was missing like level... but it's still just returning the bottom (top?) record, same as before. It's almost like it's not matching the parent_id, but when I look at it manually they definitely match. Or, it's not doing the recursion. I'll keep at it, I appreciate your help.
– Cal Harris
Nov 20 '18 at 19:29
@Clockwork-Muse when I try to use any type of Join it errors and says 'an explicit join including an ON clause is not allowed in the fullselect of the recursive operation'
– Cal Harris
Nov 20 '18 at 19:36
Update: My apologies, your solution worked fine. We had the parent_id trying to match the STCC_RECORD_ID instead of just ID. It's works like a champ, you're my hero. Thanks!
– Cal Harris
Nov 20 '18 at 19:40
|
show 1 more comment
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.
answered Nov 20 '18 at 18:52
data_henrikdata_henrik
9,28421031
Why the level restrictions? The OP can't know, ahead of time, how deep the tree is. Oh, and please, don't use the comma-separatedFROMclause - just explicitly list out theJOIN.
– Clockwork-Muse
Nov 20 '18 at 19:13
It says 6 levels and it is a precaution for testing.
– data_henrik
Nov 20 '18 at 19:25
Thanks, that helped with a couple of things I was missing like level... but it's still just returning the bottom (top?) record, same as before. It's almost like it's not matching the parent_id, but when I look at it manually they definitely match. Or, it's not doing the recursion. I'll keep at it, I appreciate your help.
– Cal Harris
Nov 20 '18 at 19:29
@Clockwork-Muse when I try to use any type of Join it errors and says 'an explicit join including an ON clause is not allowed in the fullselect of the recursive operation'
– Cal Harris
Nov 20 '18 at 19:36
Update: My apologies, your solution worked fine. We had the parent_id trying to match the STCC_RECORD_ID instead of just ID. It's works like a champ, you're my hero. Thanks!
– Cal Harris
Nov 20 '18 at 19:40
|
show 1 more comment
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.
answered Nov 20 '18 at 18:52
data_henrikdata_henrik
9,28421031
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.
answered Nov 20 '18 at 18:52
data_henrikdata_henrik
9,28421031
answered Nov 20 '18 at 18:52
data_henrikdata_henrik
9,28421031
answered Nov 20 '18 at 18:52
data_henrikdata_henrik
9,28421031
answered Nov 20 '18 at 18:52
data_henrikdata_henrik
9,28421031
9,28421031
Why the level restrictions? The OP can't know, ahead of time, how deep the tree is. Oh, and please, don't use the comma-separatedFROMclause - just explicitly list out theJOIN.
– Clockwork-Muse
Nov 20 '18 at 19:13
It says 6 levels and it is a precaution for testing.
– data_henrik
Nov 20 '18 at 19:25
Thanks, that helped with a couple of things I was missing like level... but it's still just returning the bottom (top?) record, same as before. It's almost like it's not matching the parent_id, but when I look at it manually they definitely match. Or, it's not doing the recursion. I'll keep at it, I appreciate your help.
– Cal Harris
Nov 20 '18 at 19:29
@Clockwork-Muse when I try to use any type of Join it errors and says 'an explicit join including an ON clause is not allowed in the fullselect of the recursive operation'
– Cal Harris
Nov 20 '18 at 19:36
Update: My apologies, your solution worked fine. We had the parent_id trying to match the STCC_RECORD_ID instead of just ID. It's works like a champ, you're my hero. Thanks!
– Cal Harris
Nov 20 '18 at 19:40
|
show 1 more comment
Why the level restrictions? The OP can't know, ahead of time, how deep the tree is. Oh, and please, don't use the comma-separatedFROMclause - just explicitly list out theJOIN.
– Clockwork-Muse
Nov 20 '18 at 19:13
It says 6 levels and it is a precaution for testing.
– data_henrik
Nov 20 '18 at 19:25
Thanks, that helped with a couple of things I was missing like level... but it's still just returning the bottom (top?) record, same as before. It's almost like it's not matching the parent_id, but when I look at it manually they definitely match. Or, it's not doing the recursion. I'll keep at it, I appreciate your help.
– Cal Harris
Nov 20 '18 at 19:29
@Clockwork-Muse when I try to use any type of Join it errors and says 'an explicit join including an ON clause is not allowed in the fullselect of the recursive operation'
– Cal Harris
Nov 20 '18 at 19:36
Update: My apologies, your solution worked fine. We had the parent_id trying to match the STCC_RECORD_ID instead of just ID. It's works like a champ, you're my hero. Thanks!
– Cal Harris
Nov 20 '18 at 19:40
Why the level restrictions? The OP can't know, ahead of time, how deep the tree is. Oh, and please, don't use the comma-separated
FROM clause - just explicitly list out the JOIN.– Clockwork-Muse
Nov 20 '18 at 19:13
Why the level restrictions? The OP can't know, ahead of time, how deep the tree is. Oh, and please, don't use the comma-separated
FROM clause - just explicitly list out the JOIN.– Clockwork-Muse
Nov 20 '18 at 19:13
It says 6 levels and it is a precaution for testing.
– data_henrik
Nov 20 '18 at 19:25
It says 6 levels and it is a precaution for testing.
– data_henrik
Nov 20 '18 at 19:25
Thanks, that helped with a couple of things I was missing like level... but it's still just returning the bottom (top?) record, same as before. It's almost like it's not matching the parent_id, but when I look at it manually they definitely match. Or, it's not doing the recursion. I'll keep at it, I appreciate your help.
– Cal Harris
Nov 20 '18 at 19:29
Thanks, that helped with a couple of things I was missing like level... but it's still just returning the bottom (top?) record, same as before. It's almost like it's not matching the parent_id, but when I look at it manually they definitely match. Or, it's not doing the recursion. I'll keep at it, I appreciate your help.
– Cal Harris
Nov 20 '18 at 19:29
@Clockwork-Muse when I try to use any type of Join it errors and says 'an explicit join including an ON clause is not allowed in the fullselect of the recursive operation'
– Cal Harris
Nov 20 '18 at 19:36
@Clockwork-Muse when I try to use any type of Join it errors and says 'an explicit join including an ON clause is not allowed in the fullselect of the recursive operation'
– Cal Harris
Nov 20 '18 at 19:36
Update: My apologies, your solution worked fine. We had the parent_id trying to match the STCC_RECORD_ID instead of just ID. It's works like a champ, you're my hero. Thanks!
– Cal Harris
Nov 20 '18 at 19:40
Update: My apologies, your solution worked fine. We had the parent_id trying to match the STCC_RECORD_ID instead of just ID. It's works like a champ, you're my hero. Thanks!
– Cal Harris
Nov 20 '18 at 19:40
|
show 1 more comment
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53399266%2fdb2-cte-recursion-on-one-table-6-levels%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
