| Select data different Schema [message #488896] |
Wed, 12 January 2011 01:33  |
kame
Messages: 69
Registered: July 2009
|
Member |
|
|
I have 3 users in schema .
User09
User10
User11
Table Name PURCHASE exists in all above three schema with different data(according to date)
Like
User09 ----> purchase ---- data date Jan09 to Dec09
User10 ----> purchase ---- data date Jan10 to Dec10
User11 ----> purchase ---- data date Jan11 to Dec11
I want to select * from purchase, from above all three user with select statement.
I can select data one by one with union all
select * from user09.pruchase Union all
select * from user10.pruchase Union all
select * from user11.pruchase
Here is one problem :-
after creating this query I add another User User12 and that User has same purchase table so I will have to add one another line IN Union all Query.
when I see this query. It gives me the all owner name of User09,User10,User11......
SELECT * FROM ALL_ALL_TABLES
WHERE OWNER LIKE 'USER%'
and table_name ='PURCHASE'
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
USER PURCHASE USERS
USER09 PURCHASE USERS
USER10 PURCHASE USERS
3 rows selected
Can I get data from Purchase Table of all users with select statement .
|
|
|
|
|
|
| Re: Select data different Schema [message #488902 is a reply to message #488899] |
Wed, 12 January 2011 02:54  |
kame
Messages: 69
Registered: July 2009
|
Member |
|
|
Union all is not a solution. because I do not want to re-write query. I want that I write my query only one time for report. every time report fetch data automatically through Select statement.
If I use union all query in report and I add one another USER User12 select statement will not consider USER12's Purchase Table.
I need to select all Purchase table data where User Like 'User%'
|
|
|
|
|
|
| Re: Select data different Schema [message #488908 is a reply to message #488905] |
Wed, 12 January 2011 03:17 |
 |
Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
What made you create such a model? Why don't you have a single PURCHASE table with (additional, if necessary) DATE column? Doing so, you'd be able to select any data you are interested in, using a simple SELECT statement.
What you have now (and what comes in the future) is simply horrible.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
