Merge tables in a view

[ssmith]

I have 4 tables : caa98 caa99 caa00 caa01, there are some differences with column names, datatypes and extra columns between the tables. I now have a user who wants to merge the data, and wants to see all columns in each table. How can I create a view on these tables?

[anandkl]

Create view select * from table1,table2,table3,table4

[ssmith]

If I do
create view all_caa
as select * from caa97,caa98,caa99,caa00,caa01;
I get the following error:
ERROR at line 2:
ORA-00957: duplicate column name

[anandkl]

In this case you need to prefix the table name before each such column which exits in other table.

create view myview as select t1.a1 name,t2.a2 name from t1,t2;

cheese
anandkl

[ssmith]

This doesn't work either.
I am getting problems with
ORA-01790: expression must have same datatype as corresponding expression.

[ales]

Originally posted by anandkl
Create view select * from table1,table2,table3,table4

This would create a cartesian product which would be probably useless.

You have to point out columns you want to merge and columns you want to have their own place in select-list. Then make the select from the first table with placeholders for all columns from the other tables that are not mergeable with the first table.
Then merge other tables with UNION clause carefully keeping positions in select-list.

Then create a view from this query.


Consider tables A (ID, SPEC, TEXT) and B (SPEC, TEXT, COMM) all columns of character type.
We want to merge SPEC and TEXT columns, A.ID and B.COMM should have their own place.

To do that we can:

select ID, SPEC, TEXT, '?' as COMM
from A
UNION
select '?', SPEC, TEXT, COMM
from B

Read about UNION in Oracle doc.

[ssmith]

I now realised its not as easy as I had thought. I have to also translate number columns to varchar etc.