create view with wild card
I am trying to create view from series of tables, but not sure about the solution...
CREATE VIEW V1 (C1) AS SELECT SIN(C1) FROM T*;
* is the wild card because I have 100 tables T1...T100...
Is this possible in oracle?
CREATE VIEW V1 (C1) AS SELECT SIN(C1) FROM T*;
* is the wild card because I have 100 tables T1...T100...
Is this possible in oracle?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Nope.
You can write some complex XML type queries or functions that might dynamically generate the desired output but I would advise you to just bite the bullet and create a UNION or "UNION ALL view.
CREATE VIEW V1 (C1) AS
SELECT SIN(C1) FROM T1
UNION ALL
SELECT SIN(C1) FROM T2
...
You can write SQL to generate the SQL:
select 'SELECT SIN(C1) FROM || table_name || chr(13) || chr(10) || ' union all '
from user_tables where table_name like 'T%';