I replicate a transaction table from twelve remote sites to my central database. My application ( which I can't change ) runs a
"select rowid, field1, field2, field3 . . . . "
query against each table with no problem.
Trouble is, I need to merge my twelve tables into one, so my application can view data in all of the tables at the same time. When I create a view as
'create view viewname as (select * from table1) union (select * from table2) union . . . . . union (select * from table12)
my application's sql query
"select rowid, field1, field2, field3 . . . . from viewname" returns the error message 'ORA-01446: cannot select ROWID from view with DISTINCT,GROUP BY, etc' .
Any ideas on how to get round this ? Remember I cannot change my application, therefore I cannot remove the reference to rowid from the sql query. Also, I prefer to create a view ( rather than a table ) so that each day's replicated data is automatically incorporated