Merge tables in a view
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Merge tables in a view

  1. #1
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    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?

  2. #2
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Create view select * from table1,table2,table3,table4
    anandkl

  3. #3
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    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

  4. #4
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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
    anandkl

  5. #5
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    This doesn't work either.
    I am getting problems with
    ORA-01790: expression must have same datatype as corresponding expression.

  6. #6
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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:
    Code:
    select ID, SPEC, TEXT, '?' as COMM
    from A
    UNION
    select '?', SPEC, TEXT, COMM
    from B
    Read about UNION in Oracle doc.




  7. #7
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    I now realised its not as easy as I had thought. I have to also translate number columns to varchar etc.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width