Oracle: Create a View with Auto Increment id column

Question

I have created a view that fills data from different tables. I used 10 select statements and combine the results of those select statements using UNION ALL.

I want to add primary key column to my view. because I have to create XML file using data in this view. so I need a primary key column for some process in my XML building application.

I have add rownum to all my select statements. But it returned duplicate ids. because rownum in each select statements start from 1.

Then I have created a sequence and tried use nextval . But I can't use sequence because my select statements has group by and order by.

Is there any way to do that ?

Answer 1

You can do a select over the union, for example:

SELECT rownum(),*
FROM (SELECT * FROM tableA UNION ALL SELECT * FROM tableB)

UPDATED

SELECT rownum, t.*
FROM (SELECT * FROM tableA UNION ALL SELECT * FROM tableB) t