Adding a virtual column in SELECT statement from a list of value of a subquery in Oracle dynamically
I need to add a virtual column on the result of a subquery in dynamic way. I need to add n virtual column as n value inside a nested table nt for each result of the subquery. I mean, for example I have a nested table nt of 7 results, i.e. nt = {'one','two','three','four','five','six','seven'} and a subquery (a SELECT) that returns 30 result, i.e.:
FROM{
----- SUBQUERY WITH 30 RESULTS ------
}
SUBQUERY RESULTS:
ID|NAME|SURNAME|
1|JACK|BROWN|
2|BRAD|PITT|
3|ROBBIE|WILLIAMS|
.
.
.
30|JOHNNY|DEPP|
and I want to add a column for each element of the subquery result, the column of the nested table values. I mean, finally I want a subquery result rows*nested table values = 30*7 = 210 rows, obtained in a dynamic way because the nested table can be upgraded. Finally I want to obtain something like that:
ID|NAME|SURNAME|nt_value
1|JACK|BROWN|one
1|JACK|BROWN|two
1|JACK|BROWN|three
.
.
.
1|JACK|BROWN|seven
2|BRAD|PITT|one
2|BRAD|PITT|two
.
.
.
2|BRAD|PITT|seven
.
.
.
30|JOHNNY|DEPP|one
.
.
.
30|JOHNNY|DEPP|seven
(Main query)
FROM { SELECT id, name, surname, [nt] AS nt_value FROM artist}
(query main continue)
I don't want to insert a plsql FOR...LOOP cycle because the main query is too big and have several subqueries, thus I don't want to make a UNION with each result of the total query made by a iteration of the index inside nt
sql oracle plsql subquery nested-table
edited Nov 21 '18 at 13:41
Kaushik Nayak
20.9k41332
asked Nov 21 '18 at 13:32
Alfonso SilvestriAlfonso Silvestri
3211718
add a comment |
I need to add a virtual column on the result of a subquery in dynamic way. I need to add n virtual column as n value inside a nested table nt for each result of the subquery. I mean, for example I have a nested table nt of 7 results, i.e. nt = {'one','two','three','four','five','six','seven'} and a subquery (a SELECT) that returns 30 result, i.e.:
FROM{
----- SUBQUERY WITH 30 RESULTS ------
}
SUBQUERY RESULTS:
ID|NAME|SURNAME|
1|JACK|BROWN|
2|BRAD|PITT|
3|ROBBIE|WILLIAMS|
.
.
.
30|JOHNNY|DEPP|
and I want to add a column for each element of the subquery result, the column of the nested table values. I mean, finally I want a subquery result rows*nested table values = 30*7 = 210 rows, obtained in a dynamic way because the nested table can be upgraded. Finally I want to obtain something like that:
ID|NAME|SURNAME|nt_value
1|JACK|BROWN|one
1|JACK|BROWN|two
1|JACK|BROWN|three
.
.
.
1|JACK|BROWN|seven
2|BRAD|PITT|one
2|BRAD|PITT|two
.
.
.
2|BRAD|PITT|seven
.
.
.
30|JOHNNY|DEPP|one
.
.
.
30|JOHNNY|DEPP|seven
(Main query)
FROM { SELECT id, name, surname, [nt] AS nt_value FROM artist}
(query main continue)
I don't want to insert a plsql FOR...LOOP cycle because the main query is too big and have several subqueries, thus I don't want to make a UNION with each result of the total query made by a iteration of the index inside nt
sql oracle plsql subquery nested-table
edited Nov 21 '18 at 13:41
Kaushik Nayak
20.9k41332
asked Nov 21 '18 at 13:32
Alfonso SilvestriAlfonso Silvestri
3211718
1
Please dont spam tag other RDBMS.
– Madhur Bhaiya
Nov 21 '18 at 13:33
add a comment |
I need to add a virtual column on the result of a subquery in dynamic way. I need to add n virtual column as n value inside a nested table nt for each result of the subquery. I mean, for example I have a nested table nt of 7 results, i.e. nt = {'one','two','three','four','five','six','seven'} and a subquery (a SELECT) that returns 30 result, i.e.:
FROM{
----- SUBQUERY WITH 30 RESULTS ------
}
SUBQUERY RESULTS:
ID|NAME|SURNAME|
1|JACK|BROWN|
2|BRAD|PITT|
3|ROBBIE|WILLIAMS|
.
.
.
30|JOHNNY|DEPP|
and I want to add a column for each element of the subquery result, the column of the nested table values. I mean, finally I want a subquery result rows*nested table values = 30*7 = 210 rows, obtained in a dynamic way because the nested table can be upgraded. Finally I want to obtain something like that:
ID|NAME|SURNAME|nt_value
1|JACK|BROWN|one
1|JACK|BROWN|two
1|JACK|BROWN|three
.
.
.
1|JACK|BROWN|seven
2|BRAD|PITT|one
2|BRAD|PITT|two
.
.
.
2|BRAD|PITT|seven
.
.
.
30|JOHNNY|DEPP|one
.
.
.
30|JOHNNY|DEPP|seven
(Main query)
FROM { SELECT id, name, surname, [nt] AS nt_value FROM artist}
(query main continue)
I don't want to insert a plsql FOR...LOOP cycle because the main query is too big and have several subqueries, thus I don't want to make a UNION with each result of the total query made by a iteration of the index inside nt
sql oracle plsql subquery nested-table
edited Nov 21 '18 at 13:41
Kaushik Nayak
20.9k41332
asked Nov 21 '18 at 13:32
Alfonso SilvestriAlfonso Silvestri
3211718
I need to add a virtual column on the result of a subquery in dynamic way. I need to add n virtual column as n value inside a nested table nt for each result of the subquery. I mean, for example I have a nested table nt of 7 results, i.e. nt = {'one','two','three','four','five','six','seven'} and a subquery (a SELECT) that returns 30 result, i.e.:
FROM{
----- SUBQUERY WITH 30 RESULTS ------
}
SUBQUERY RESULTS:
ID|NAME|SURNAME|
1|JACK|BROWN|
2|BRAD|PITT|
3|ROBBIE|WILLIAMS|
.
.
.
30|JOHNNY|DEPP|
and I want to add a column for each element of the subquery result, the column of the nested table values. I mean, finally I want a subquery result rows*nested table values = 30*7 = 210 rows, obtained in a dynamic way because the nested table can be upgraded. Finally I want to obtain something like that:
ID|NAME|SURNAME|nt_value
1|JACK|BROWN|one
1|JACK|BROWN|two
1|JACK|BROWN|three
.
.
.
1|JACK|BROWN|seven
2|BRAD|PITT|one
2|BRAD|PITT|two
.
.
.
2|BRAD|PITT|seven
.
.
.
30|JOHNNY|DEPP|one
.
.
.
30|JOHNNY|DEPP|seven
(Main query)
FROM { SELECT id, name, surname, [nt] AS nt_value FROM artist}
(query main continue)
I don't want to insert a plsql FOR...LOOP cycle because the main query is too big and have several subqueries, thus I don't want to make a UNION with each result of the total query made by a iteration of the index inside nt
sql oracle plsql subquery nested-table
sql oracle plsql subquery nested-table
edited Nov 21 '18 at 13:41
Kaushik Nayak
20.9k41332
asked Nov 21 '18 at 13:32
Alfonso SilvestriAlfonso Silvestri
3211718
edited Nov 21 '18 at 13:41
Kaushik Nayak
20.9k41332
asked Nov 21 '18 at 13:32
Alfonso SilvestriAlfonso Silvestri
3211718
edited Nov 21 '18 at 13:41
Kaushik Nayak
20.9k41332
edited Nov 21 '18 at 13:41
Kaushik Nayak
20.9k41332
edited Nov 21 '18 at 13:41
Kaushik Nayak
20.9k41332
20.9k41332
asked Nov 21 '18 at 13:32
Alfonso SilvestriAlfonso Silvestri
3211718
asked Nov 21 '18 at 13:32
Alfonso SilvestriAlfonso Silvestri
3211718
asked Nov 21 '18 at 13:32
Alfonso SilvestriAlfonso Silvestri
3211718
3211718
1
Please dont spam tag other RDBMS.
– Madhur Bhaiya
Nov 21 '18 at 13:33
add a comment |
1
Please dont spam tag other RDBMS.
– Madhur Bhaiya
Nov 21 '18 at 13:33
1
1
Please dont spam tag other RDBMS.
– Madhur Bhaiya
Nov 21 '18 at 13:33
Please dont spam tag other RDBMS.
– Madhur Bhaiya
Nov 21 '18 at 13:33
add a comment |
2 Answers
2
active
oldest
votes
Cross join the two outputs:
SELECT * FROM
(/*put query that returns 30 rows here*/) q30
CROSS JOIN
(/*query that returns 10 rows here*/) q7
Used like:
SELECT * FROM
/* other tables or subqueries here */
WHATEVER JOIN
(
SELECT * FROM
(/*put query that returns 30 rows here*/) q30
CROSS JOIN
(/*query that returns 10 rows here*/) q7
) q210
ON (...)
answered Nov 21 '18 at 13:44
Caius JardCaius Jard
12.5k21340
Thank you. Let me check the results, maybe it works
– Alfonso Silvestri
Nov 21 '18 at 14:08
add a comment |
You may cross join your table/query result with the output of TABLE function on the nested table.
CREATE OR REPLACE TYPE tab_nested_type AS TABLE OF VARCHAR2(10);
/
SELECT t.*,
st.column_value AS nt_value
FROM t --or your subquery
CROSS JOIN
TABLE ( tab_nested_type('one','two','three','four','five','six','seven') ) st
ORDER BY id;
If you are on Oracle 12.2 or above, you don't even need to specify the TABLE()
Demo
answered Nov 21 '18 at 13:55
Kaushik NayakKaushik Nayak
20.9k41332
This solutions doesn't work beacause of I need the name of the column given by the nested table. I mean, how I can add an alias on the column in the nested table?
– Alfonso Silvestri
Nov 21 '18 at 14:35
@Alfonso : I'm not sure what you are asking. This answers the original question for the details you provided.If you have further questions ask another one showing fully what you were actually trying to do.
– Kaushik Nayak
Nov 21 '18 at 14:51
It works! Thank yoy very much. +1
– Alfonso Silvestri
Nov 21 '18 at 15:32
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
});
}
});
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%2f53413193%2fadding-a-virtual-column-in-select-statement-from-a-list-of-value-of-a-subquery-i%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Cross join the two outputs:
SELECT * FROM
(/*put query that returns 30 rows here*/) q30
CROSS JOIN
(/*query that returns 10 rows here*/) q7
Used like:
SELECT * FROM
/* other tables or subqueries here */
WHATEVER JOIN
(
SELECT * FROM
(/*put query that returns 30 rows here*/) q30
CROSS JOIN
(/*query that returns 10 rows here*/) q7
) q210
ON (...)
answered Nov 21 '18 at 13:44
Caius JardCaius Jard
12.5k21340
Thank you. Let me check the results, maybe it works
– Alfonso Silvestri
Nov 21 '18 at 14:08
add a comment |
Cross join the two outputs:
SELECT * FROM
(/*put query that returns 30 rows here*/) q30
CROSS JOIN
(/*query that returns 10 rows here*/) q7
Used like:
SELECT * FROM
/* other tables or subqueries here */
WHATEVER JOIN
(
SELECT * FROM
(/*put query that returns 30 rows here*/) q30
CROSS JOIN
(/*query that returns 10 rows here*/) q7
) q210
ON (...)
answered Nov 21 '18 at 13:44
Caius JardCaius Jard
12.5k21340
Thank you. Let me check the results, maybe it works
– Alfonso Silvestri
Nov 21 '18 at 14:08
add a comment |
Cross join the two outputs:
SELECT * FROM
(/*put query that returns 30 rows here*/) q30
CROSS JOIN
(/*query that returns 10 rows here*/) q7
Used like:
SELECT * FROM
/* other tables or subqueries here */
WHATEVER JOIN
(
SELECT * FROM
(/*put query that returns 30 rows here*/) q30
CROSS JOIN
(/*query that returns 10 rows here*/) q7
) q210
ON (...)
answered Nov 21 '18 at 13:44
Caius JardCaius Jard
12.5k21340
Cross join the two outputs:
SELECT * FROM
(/*put query that returns 30 rows here*/) q30
CROSS JOIN
(/*query that returns 10 rows here*/) q7
Used like:
SELECT * FROM
/* other tables or subqueries here */
WHATEVER JOIN
(
SELECT * FROM
(/*put query that returns 30 rows here*/) q30
CROSS JOIN
(/*query that returns 10 rows here*/) q7
) q210
ON (...)
answered Nov 21 '18 at 13:44
Caius JardCaius Jard
12.5k21340
answered Nov 21 '18 at 13:44
Caius JardCaius Jard
12.5k21340
answered Nov 21 '18 at 13:44
Caius JardCaius Jard
12.5k21340
answered Nov 21 '18 at 13:44
Caius JardCaius Jard
12.5k21340
12.5k21340
Thank you. Let me check the results, maybe it works
– Alfonso Silvestri
Nov 21 '18 at 14:08
add a comment |
Thank you. Let me check the results, maybe it works
– Alfonso Silvestri
Nov 21 '18 at 14:08
Thank you. Let me check the results, maybe it works
– Alfonso Silvestri
Nov 21 '18 at 14:08
Thank you. Let me check the results, maybe it works
– Alfonso Silvestri
Nov 21 '18 at 14:08
add a comment |
You may cross join your table/query result with the output of TABLE function on the nested table.
CREATE OR REPLACE TYPE tab_nested_type AS TABLE OF VARCHAR2(10);
/
SELECT t.*,
st.column_value AS nt_value
FROM t --or your subquery
CROSS JOIN
TABLE ( tab_nested_type('one','two','three','four','five','six','seven') ) st
ORDER BY id;
If you are on Oracle 12.2 or above, you don't even need to specify the TABLE()
Demo
answered Nov 21 '18 at 13:55
Kaushik NayakKaushik Nayak
20.9k41332
This solutions doesn't work beacause of I need the name of the column given by the nested table. I mean, how I can add an alias on the column in the nested table?
– Alfonso Silvestri
Nov 21 '18 at 14:35
@Alfonso : I'm not sure what you are asking. This answers the original question for the details you provided.If you have further questions ask another one showing fully what you were actually trying to do.
– Kaushik Nayak
Nov 21 '18 at 14:51
It works! Thank yoy very much. +1
– Alfonso Silvestri
Nov 21 '18 at 15:32
add a comment |
You may cross join your table/query result with the output of TABLE function on the nested table.
CREATE OR REPLACE TYPE tab_nested_type AS TABLE OF VARCHAR2(10);
/
SELECT t.*,
st.column_value AS nt_value
FROM t --or your subquery
CROSS JOIN
TABLE ( tab_nested_type('one','two','three','four','five','six','seven') ) st
ORDER BY id;
If you are on Oracle 12.2 or above, you don't even need to specify the TABLE()
Demo
answered Nov 21 '18 at 13:55
Kaushik NayakKaushik Nayak
20.9k41332
This solutions doesn't work beacause of I need the name of the column given by the nested table. I mean, how I can add an alias on the column in the nested table?
– Alfonso Silvestri
Nov 21 '18 at 14:35
@Alfonso : I'm not sure what you are asking. This answers the original question for the details you provided.If you have further questions ask another one showing fully what you were actually trying to do.
– Kaushik Nayak
Nov 21 '18 at 14:51
It works! Thank yoy very much. +1
– Alfonso Silvestri
Nov 21 '18 at 15:32
add a comment |
You may cross join your table/query result with the output of TABLE function on the nested table.
CREATE OR REPLACE TYPE tab_nested_type AS TABLE OF VARCHAR2(10);
/
SELECT t.*,
st.column_value AS nt_value
FROM t --or your subquery
CROSS JOIN
TABLE ( tab_nested_type('one','two','three','four','five','six','seven') ) st
ORDER BY id;
If you are on Oracle 12.2 or above, you don't even need to specify the TABLE()
Demo
answered Nov 21 '18 at 13:55
Kaushik NayakKaushik Nayak
20.9k41332
You may cross join your table/query result with the output of TABLE function on the nested table.
CREATE OR REPLACE TYPE tab_nested_type AS TABLE OF VARCHAR2(10);
/
SELECT t.*,
st.column_value AS nt_value
FROM t --or your subquery
CROSS JOIN
TABLE ( tab_nested_type('one','two','three','four','five','six','seven') ) st
ORDER BY id;
If you are on Oracle 12.2 or above, you don't even need to specify the TABLE()
Demo
answered Nov 21 '18 at 13:55
Kaushik NayakKaushik Nayak
20.9k41332
edited Nov 21 '18 at 14:15
answered Nov 21 '18 at 13:55
Kaushik NayakKaushik Nayak
20.9k41332
answered Nov 21 '18 at 13:55
Kaushik NayakKaushik Nayak
20.9k41332
answered Nov 21 '18 at 13:55
Kaushik NayakKaushik Nayak
20.9k41332
20.9k41332
This solutions doesn't work beacause of I need the name of the column given by the nested table. I mean, how I can add an alias on the column in the nested table?
– Alfonso Silvestri
Nov 21 '18 at 14:35
@Alfonso : I'm not sure what you are asking. This answers the original question for the details you provided.If you have further questions ask another one showing fully what you were actually trying to do.
– Kaushik Nayak
Nov 21 '18 at 14:51
It works! Thank yoy very much. +1
– Alfonso Silvestri
Nov 21 '18 at 15:32
add a comment |
This solutions doesn't work beacause of I need the name of the column given by the nested table. I mean, how I can add an alias on the column in the nested table?
– Alfonso Silvestri
Nov 21 '18 at 14:35
@Alfonso : I'm not sure what you are asking. This answers the original question for the details you provided.If you have further questions ask another one showing fully what you were actually trying to do.
– Kaushik Nayak
Nov 21 '18 at 14:51
It works! Thank yoy very much. +1
– Alfonso Silvestri
Nov 21 '18 at 15:32
This solutions doesn't work beacause of I need the name of the column given by the nested table. I mean, how I can add an alias on the column in the nested table?
– Alfonso Silvestri
Nov 21 '18 at 14:35
This solutions doesn't work beacause of I need the name of the column given by the nested table. I mean, how I can add an alias on the column in the nested table?
– Alfonso Silvestri
Nov 21 '18 at 14:35
@Alfonso : I'm not sure what you are asking. This answers the original question for the details you provided.If you have further questions ask another one showing fully what you were actually trying to do.
– Kaushik Nayak
Nov 21 '18 at 14:51
@Alfonso : I'm not sure what you are asking. This answers the original question for the details you provided.If you have further questions ask another one showing fully what you were actually trying to do.
– Kaushik Nayak
Nov 21 '18 at 14:51
It works! Thank yoy very much. +1
– Alfonso Silvestri
Nov 21 '18 at 15:32
It works! Thank yoy very much. +1
– Alfonso Silvestri
Nov 21 '18 at 15:32
add a 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%2f53413193%2fadding-a-virtual-column-in-select-statement-from-a-list-of-value-of-a-subquery-i%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
1
Please dont spam tag other RDBMS.
– Madhur Bhaiya
Nov 21 '18 at 13:33