Adding a virtual column in SELECT statement from a list of value of a subquery in Oracle dynamically












1















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







share|improve this question




















  • 1





    Please dont spam tag other RDBMS.

    – Madhur Bhaiya
    Nov 21 '18 at 13:33
















1















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







share|improve this question




















  • 1





    Please dont spam tag other RDBMS.

    – Madhur Bhaiya
    Nov 21 '18 at 13:33














1












1








1








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







share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 21 '18 at 13:41









Kaushik Nayak

20.9k41332




20.9k41332










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














  • 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












2 Answers
2






active

oldest

votes


















1














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 (...)





share|improve this answer
























  • Thank you. Let me check the results, maybe it works

    – Alfonso Silvestri
    Nov 21 '18 at 14:08





















3














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






share|improve this answer


























  • 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











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


















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









1














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 (...)





share|improve this answer
























  • Thank you. Let me check the results, maybe it works

    – Alfonso Silvestri
    Nov 21 '18 at 14:08


















1














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 (...)





share|improve this answer
























  • Thank you. Let me check the results, maybe it works

    – Alfonso Silvestri
    Nov 21 '18 at 14:08
















1












1








1







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 (...)





share|improve this answer













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 (...)






share|improve this answer












share|improve this answer



share|improve this answer










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





















  • 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















3














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






share|improve this answer


























  • 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
















3














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






share|improve this answer


























  • 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














3












3








3







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






share|improve this answer















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







share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 21 '18 at 14:15

























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



















  • 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


















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














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





















































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







-

Popular posts from this blog

Guess what letter conforming each word

Image
6 $begingroup$ This is an entry in the Fortnightly Topic Challenge #41: Short and Sweet. 1 letter only could be mean anything . With an additional letter in front , could be used as medicine component and something you definitely learn or at least heard once. Add another letter in front , you would possibly hear that many times . Add an additional letter in front , you would found an independent country . With an additional letter, in the end , you would found something related to apple . Add another letter in the end , you would found something bad . Hint: Each letter is unique . word knowledge letters chemistry share | improve this question ...
Read more

Port of Spain

Image
For ports in the country of Spain, see List of ports in Spain. City in City of Port of Spain, Trinidad and Tobago Port of Spain City City of Port of Spain Top to bottom (left to right): Skyline of Port of Spain from Gulf of Paria; National Academy for the Performing Arts, Independence Square, Eric Williams Plaza; MovieTowne, Port of Port of Spain Motto(s):  "We Gather Strength As We Go Along" Port of Spain Coordinates: 10°40′N 61°31′W  /  10.667°N 61.517°W  / 10.667; -61.517 Coordinates: 10°40′N 61°31′W  /  10.667°N 61.517°W  / 10.667; -61.517 Country   Trinidad and Tobago Jurisdiction City of Port of Spain Settled 1560 Incorporated (town) 1853 Incorporated (city) 1914 Government  • Mayor Joel Martinez  • Governing body City Corporation Area [1]  • City 12 km 2 (5 sq mi) Elevation [2] 66 m (217 ft) Population (2011) [1]  • City 37,074  • Rank 3rd  • Density 3,090/km 2 (8,000/sq mi)...
Read more

Run scheduled task as local user group (not BUILTIN)

Image
0 I am trying to run a scheduled task with User Group (not a single user). This is working fine when I in the field "when running a task, use the following user account:" insert BUILTIN User Group (for example: Administrators or Users). But when I try it with the created Local User Group (example: TestGroup) it is not working. The Users in the TestGroup are the same as in the Administrators group. What could be the problem? Are there some restrictions to the Created User Group compared to BUILTINUsers? scheduled-tasks taskscheduler windowstaskschedule share | improve this question asked Nov 15 '18 at 15:36 Far...
Read more