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