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

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

Please dont spam tag other RDBMS. – Madhur Bhaiya Nov 21 '18 at 13:33 — Madhur Bhaiya, Nov 21 '18 at 13:33

Kaushik Nayak · Answer 1 · 2018-11-21T14:15:01.830

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

edited Nov 21 '18 at 14:15

answered Nov 21 '18 at 13:55

Kaushik Nayak

30,772
5
32
45

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? – Giacomo Brunetta 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

score 1 · Accepted Answer · answered Nov 21 '18 at 13:44

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

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

2 Answers2