CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Pavan Kumar
Pavan Kumar

Reputation: 3

I want to write multiple queries results one after another in oracle

For ex : if there are 3 sql statements and it produces 2 rows per each sql.. it should display the result in below format 

Row 1 from sql1
Row 1 from sql2
Row 1 from sql3
Row 2 from sql1
Row 2 from sql2
Row 2 from sql3

I tried union thinking it may help but it prints first sql statement results and goes to next one.

Any help would be highly appreciated

Upvotes: 0

Views: 348

Answers (1)

Popeye
Popeye

Reputation: 35930

You can take advantage of ROWNUM here:

SELECT <COLUM_LIST> FROM
(SELECT <COLUM_LIST>, ROWNUM AS RN, 1 AS QUERY_NUM from table1 -- sql query 1
UNION ALL
SELECT <COLUM_LIST>, ROWNUM AS RN, 2 AS QUERY_NUM from table2 -- sql query 2
UNION ALL
SELECT <COLUM_LIST>, ROWNUM AS RN, 3 AS QUERY_NUM from table3 -- sql query 3
)
ORDER BY RN, QUERY_NUM

If you want to define specific order of record in each query then instead of ROWNUM, you can use ROW_NUMBER analytical function.

Cheers!!

Upvotes: 1

Related Questions