Oracle Complex Hierarchy
0
In general, I have a Key (Contract) that changes to another key in a row.
The changed Key can be changed and for that we have another row.
The key go back as it was at the beginning.
I need a row for the first Key (where it all started) and the newest Key (that is skipping all the other keys that were in between).
This creates the table:
CREATE TABLE CONTRACT ("NAME" VARCHAR2(20 BYTE)
,"OLD_CONTRACT" VARCHAR2(20 BYTE)
,"NEW_CONTRACT" VARCHAR2(20 BYTE)
,"NEW_CONTRACT_DATE" NUMBER(10)) ;
COMMIT;
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('John','1','10',20180101);
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('Ronnie','10','6',20180107);
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('Kim','6','1',20180128);
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('Nathaly','3','2',20180419);
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('Gorge','2','8',20180713);
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('Allen','8','20',20180921);
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('Tom','4','11',20170103);
COMMIT;
This is how it looks
Name old_key new Key Updated date
---- ------- ------- ------------
John 1 10 20180101
Ronnie 10 6 20180107
Kim 6 1 20180128
Nathaly 3 2 20180419
Gorge 2 8 20180713
Allen 8 20 20180921
Tom 4 11 20170103
First 3 rows returns only ONE ROW.
Old Key New Key
------- -------
1 1
Why? Because
1 Becomes 10
10 Becomes 6
6 Becomes 1
Next 3 rows returns only ONE ROW.
Old Key New Key
------- -------
3 20
Why? Because
3 Becomes 2
2 Becomes 8
8 Becomes 20
Last row returns ONE ROW.
Old Key New Key
------- -------
4 11
Why? Because
4 Becomes 11
I need help writing Query for this scenario
sql oracle hierarchy
edited Nov 21 '18 at 11:06
APC
120k15118230
asked Nov 21 '18 at 8:42
RoniRoni
42
add a comment |
0
In general, I have a Key (Contract) that changes to another key in a row.
The changed Key can be changed and for that we have another row.
The key go back as it was at the beginning.
I need a row for the first Key (where it all started) and the newest Key (that is skipping all the other keys that were in between).
This creates the table:
CREATE TABLE CONTRACT ("NAME" VARCHAR2(20 BYTE)
,"OLD_CONTRACT" VARCHAR2(20 BYTE)
,"NEW_CONTRACT" VARCHAR2(20 BYTE)
,"NEW_CONTRACT_DATE" NUMBER(10)) ;
COMMIT;
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('John','1','10',20180101);
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('Ronnie','10','6',20180107);
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('Kim','6','1',20180128);
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('Nathaly','3','2',20180419);
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('Gorge','2','8',20180713);
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('Allen','8','20',20180921);
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('Tom','4','11',20170103);
COMMIT;
This is how it looks
Name old_key new Key Updated date
---- ------- ------- ------------
John 1 10 20180101
Ronnie 10 6 20180107
Kim 6 1 20180128
Nathaly 3 2 20180419
Gorge 2 8 20180713
Allen 8 20 20180921
Tom 4 11 20170103
First 3 rows returns only ONE ROW.
Old Key New Key
------- -------
1 1
Why? Because
1 Becomes 10
10 Becomes 6
6 Becomes 1
Next 3 rows returns only ONE ROW.
Old Key New Key
------- -------
3 20
Why? Because
3 Becomes 2
2 Becomes 8
8 Becomes 20
Last row returns ONE ROW.
Old Key New Key
------- -------
4 11
Why? Because
4 Becomes 11
I need help writing Query for this scenario
sql oracle hierarchy
edited Nov 21 '18 at 11:06
APC
120k15118230
asked Nov 21 '18 at 8:42
RoniRoni
42
Could you provide expected output? Only 2 columnsOld KeyandNew Key?
– Pham X. Bach
Nov 21 '18 at 9:12
So what are the rules for deciding what value ofold_keyto start with? The first example1 -> 1seems particularly troubling.
– APC
Nov 21 '18 at 11:05
"Could you provide expected output?" .... Yes. 2 Columns- Old key & New Key "So what are the rules for deciding what value..." There are many keys , there is no main Key
– Roni
Nov 22 '18 at 9:33
add a comment |
0
0
0
In general, I have a Key (Contract) that changes to another key in a row.
The changed Key can be changed and for that we have another row.
The key go back as it was at the beginning.
I need a row for the first Key (where it all started) and the newest Key (that is skipping all the other keys that were in between).
This creates the table:
CREATE TABLE CONTRACT ("NAME" VARCHAR2(20 BYTE)
,"OLD_CONTRACT" VARCHAR2(20 BYTE)
,"NEW_CONTRACT" VARCHAR2(20 BYTE)
,"NEW_CONTRACT_DATE" NUMBER(10)) ;
COMMIT;
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('John','1','10',20180101);
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('Ronnie','10','6',20180107);
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('Kim','6','1',20180128);
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('Nathaly','3','2',20180419);
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('Gorge','2','8',20180713);
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('Allen','8','20',20180921);
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('Tom','4','11',20170103);
COMMIT;
This is how it looks
Name old_key new Key Updated date
---- ------- ------- ------------
John 1 10 20180101
Ronnie 10 6 20180107
Kim 6 1 20180128
Nathaly 3 2 20180419
Gorge 2 8 20180713
Allen 8 20 20180921
Tom 4 11 20170103
First 3 rows returns only ONE ROW.
Old Key New Key
------- -------
1 1
Why? Because
1 Becomes 10
10 Becomes 6
6 Becomes 1
Next 3 rows returns only ONE ROW.
Old Key New Key
------- -------
3 20
Why? Because
3 Becomes 2
2 Becomes 8
8 Becomes 20
Last row returns ONE ROW.
Old Key New Key
------- -------
4 11
Why? Because
4 Becomes 11
I need help writing Query for this scenario
sql oracle hierarchy
edited Nov 21 '18 at 11:06
APC
120k15118230
asked Nov 21 '18 at 8:42
RoniRoni
42
In general, I have a Key (Contract) that changes to another key in a row.
The changed Key can be changed and for that we have another row.
The key go back as it was at the beginning.
I need a row for the first Key (where it all started) and the newest Key (that is skipping all the other keys that were in between).
This creates the table:
CREATE TABLE CONTRACT ("NAME" VARCHAR2(20 BYTE)
,"OLD_CONTRACT" VARCHAR2(20 BYTE)
,"NEW_CONTRACT" VARCHAR2(20 BYTE)
,"NEW_CONTRACT_DATE" NUMBER(10)) ;
COMMIT;
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('John','1','10',20180101);
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('Ronnie','10','6',20180107);
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('Kim','6','1',20180128);
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('Nathaly','3','2',20180419);
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('Gorge','2','8',20180713);
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('Allen','8','20',20180921);
INSERT INTO CONTRACT (NAME,OLD_CONTRACT,NEW_CONTRACT,NEW_CONTRACT_DATE)
VALUES ('Tom','4','11',20170103);
COMMIT;
This is how it looks
Name old_key new Key Updated date
---- ------- ------- ------------
John 1 10 20180101
Ronnie 10 6 20180107
Kim 6 1 20180128
Nathaly 3 2 20180419
Gorge 2 8 20180713
Allen 8 20 20180921
Tom 4 11 20170103
First 3 rows returns only ONE ROW.
Old Key New Key
------- -------
1 1
Why? Because
1 Becomes 10
10 Becomes 6
6 Becomes 1
Next 3 rows returns only ONE ROW.
Old Key New Key
------- -------
3 20
Why? Because
3 Becomes 2
2 Becomes 8
8 Becomes 20
Last row returns ONE ROW.
Old Key New Key
------- -------
4 11
Why? Because
4 Becomes 11
I need help writing Query for this scenario
sql oracle hierarchy
sql oracle hierarchy
edited Nov 21 '18 at 11:06
APC
120k15118230
asked Nov 21 '18 at 8:42
RoniRoni
42
edited Nov 21 '18 at 11:06
APC
120k15118230
asked Nov 21 '18 at 8:42
RoniRoni
42
edited Nov 21 '18 at 11:06
APC
120k15118230
edited Nov 21 '18 at 11:06
APC
120k15118230
edited Nov 21 '18 at 11:06
APC
120k15118230
120k15118230
asked Nov 21 '18 at 8:42
RoniRoni
42
asked Nov 21 '18 at 8:42
RoniRoni
42
asked Nov 21 '18 at 8:42
RoniRoni
42
42
Could you provide expected output? Only 2 columnsOld KeyandNew Key?
– Pham X. Bach
Nov 21 '18 at 9:12
So what are the rules for deciding what value ofold_keyto start with? The first example1 -> 1seems particularly troubling.
– APC
Nov 21 '18 at 11:05
"Could you provide expected output?" .... Yes. 2 Columns- Old key & New Key "So what are the rules for deciding what value..." There are many keys , there is no main Key
– Roni
Nov 22 '18 at 9:33
add a comment |
Could you provide expected output? Only 2 columnsOld KeyandNew Key?
– Pham X. Bach
Nov 21 '18 at 9:12
So what are the rules for deciding what value ofold_keyto start with? The first example1 -> 1seems particularly troubling.
– APC
Nov 21 '18 at 11:05
"Could you provide expected output?" .... Yes. 2 Columns- Old key & New Key "So what are the rules for deciding what value..." There are many keys , there is no main Key
– Roni
Nov 22 '18 at 9:33
Could you provide expected output? Only 2 columns
Old Key and New Key?– Pham X. Bach
Nov 21 '18 at 9:12
Could you provide expected output? Only 2 columns
Old Key and New Key?– Pham X. Bach
Nov 21 '18 at 9:12
So what are the rules for deciding what value of
old_key to start with? The first example 1 -> 1 seems particularly troubling.– APC
Nov 21 '18 at 11:05
So what are the rules for deciding what value of
old_key to start with? The first example 1 -> 1 seems particularly troubling.– APC
Nov 21 '18 at 11:05
"Could you provide expected output?" .... Yes. 2 Columns- Old key & New Key "So what are the rules for deciding what value..." There are many keys , there is no main Key
– Roni
Nov 22 '18 at 9:33
"Could you provide expected output?" .... Yes. 2 Columns- Old key & New Key "So what are the rules for deciding what value..." There are many keys , there is no main Key
– Roni
Nov 22 '18 at 9:33
add a comment |
1 Answer
1
active
oldest
votes
0
Here is a query using CONNECT BY that does the connection that you want and need.
From its results you have to select the first old_contract and the last new_contract.
SELECT NAME,OLD_CONTRACT,NEW_CONTRACT, LEVEL, rownum
FROM contract
START WITH OLD_CONTRACT = 3
CONNECT BY NOCYCLE PRIOR NEW_CONTRACT = OLD_CONTRACT
Results
NAME OLD_CONTRACT NEW_CONTRACT LEVEL ROWNUM
Nathaly 3 2 1 1
Gorge 2 8 2 2
Allen 8 20 3 3
answered Nov 21 '18 at 13:11
devdimidevdimi
2,2211716
Thank you! It canno't have "START WITH" because the are many keys at the same level. second , the key is varchar so there is no ordering. third , you didn't mention how to identify the "group" to take the first row and the last.
– Roni
Nov 22 '18 at 9:39
add a 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
});
}
});
draft saved
draft discarded
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%2f53408150%2foracle-complex-hierarchy%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
0
Here is a query using CONNECT BY that does the connection that you want and need.
From its results you have to select the first old_contract and the last new_contract.
SELECT NAME,OLD_CONTRACT,NEW_CONTRACT, LEVEL, rownum
FROM contract
START WITH OLD_CONTRACT = 3
CONNECT BY NOCYCLE PRIOR NEW_CONTRACT = OLD_CONTRACT
Results
NAME OLD_CONTRACT NEW_CONTRACT LEVEL ROWNUM
Nathaly 3 2 1 1
Gorge 2 8 2 2
Allen 8 20 3 3
answered Nov 21 '18 at 13:11
devdimidevdimi
2,2211716
Thank you! It canno't have "START WITH" because the are many keys at the same level. second , the key is varchar so there is no ordering. third , you didn't mention how to identify the "group" to take the first row and the last.
– Roni
Nov 22 '18 at 9:39
add a comment |
0
Here is a query using CONNECT BY that does the connection that you want and need.
From its results you have to select the first old_contract and the last new_contract.
SELECT NAME,OLD_CONTRACT,NEW_CONTRACT, LEVEL, rownum
FROM contract
START WITH OLD_CONTRACT = 3
CONNECT BY NOCYCLE PRIOR NEW_CONTRACT = OLD_CONTRACT
Results
NAME OLD_CONTRACT NEW_CONTRACT LEVEL ROWNUM
Nathaly 3 2 1 1
Gorge 2 8 2 2
Allen 8 20 3 3
answered Nov 21 '18 at 13:11
devdimidevdimi
2,2211716
Thank you! It canno't have "START WITH" because the are many keys at the same level. second , the key is varchar so there is no ordering. third , you didn't mention how to identify the "group" to take the first row and the last.
– Roni
Nov 22 '18 at 9:39
add a comment |
0
0
0
Here is a query using CONNECT BY that does the connection that you want and need.
From its results you have to select the first old_contract and the last new_contract.
SELECT NAME,OLD_CONTRACT,NEW_CONTRACT, LEVEL, rownum
FROM contract
START WITH OLD_CONTRACT = 3
CONNECT BY NOCYCLE PRIOR NEW_CONTRACT = OLD_CONTRACT
Results
NAME OLD_CONTRACT NEW_CONTRACT LEVEL ROWNUM
Nathaly 3 2 1 1
Gorge 2 8 2 2
Allen 8 20 3 3
answered Nov 21 '18 at 13:11
devdimidevdimi
2,2211716
Here is a query using CONNECT BY that does the connection that you want and need.
From its results you have to select the first old_contract and the last new_contract.
SELECT NAME,OLD_CONTRACT,NEW_CONTRACT, LEVEL, rownum
FROM contract
START WITH OLD_CONTRACT = 3
CONNECT BY NOCYCLE PRIOR NEW_CONTRACT = OLD_CONTRACT
Results
NAME OLD_CONTRACT NEW_CONTRACT LEVEL ROWNUM
Nathaly 3 2 1 1
Gorge 2 8 2 2
Allen 8 20 3 3
answered Nov 21 '18 at 13:11
devdimidevdimi
2,2211716
edited Nov 21 '18 at 13:49
answered Nov 21 '18 at 13:11
devdimidevdimi
2,2211716
answered Nov 21 '18 at 13:11
devdimidevdimi
2,2211716
answered Nov 21 '18 at 13:11
devdimidevdimi
2,2211716
2,2211716
Thank you! It canno't have "START WITH" because the are many keys at the same level. second , the key is varchar so there is no ordering. third , you didn't mention how to identify the "group" to take the first row and the last.
– Roni
Nov 22 '18 at 9:39
add a comment |
Thank you! It canno't have "START WITH" because the are many keys at the same level. second , the key is varchar so there is no ordering. third , you didn't mention how to identify the "group" to take the first row and the last.
– Roni
Nov 22 '18 at 9:39
Thank you! It canno't have "START WITH" because the are many keys at the same level. second , the key is varchar so there is no ordering. third , you didn't mention how to identify the "group" to take the first row and the last.
– Roni
Nov 22 '18 at 9:39
Thank you! It canno't have "START WITH" because the are many keys at the same level. second , the key is varchar so there is no ordering. third , you didn't mention how to identify the "group" to take the first row and the last.
– Roni
Nov 22 '18 at 9:39
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53408150%2foracle-complex-hierarchy%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
Could you provide expected output? Only 2 columns
Old KeyandNew Key?– Pham X. Bach
Nov 21 '18 at 9:12
So what are the rules for deciding what value of
old_keyto start with? The first example1 -> 1seems particularly troubling.– APC
Nov 21 '18 at 11:05
"Could you provide expected output?" .... Yes. 2 Columns- Old key & New Key "So what are the rules for deciding what value..." There are many keys , there is no main Key
– Roni
Nov 22 '18 at 9:33