Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Posts
    1

    Question Unanswered: Merging Table Data in Oracle

    Hi everyone, I came accross this forum in hopes that someone can point me in the right direction as to how to merge dynamic data in Oracle 9i. I've done previous work with MS Access but this is the first time that I've used Oracle. Basically, I have 5 tables (Customers, Orders, OrderLine, Prodcuts, Suppliers). Three sets of each table exist from a different company so the various field types vary (ie, some fields have the last name, first name split, others have just Name, some have the address not broken down by city, state, zip, etc and yet others are set as varchar2's, numbers, etc.) Since this data is dynamic, I need to create reports merging all of this data into one temporary table / view, so that if more customers were added for example they would be in the new view. The ultimate result is to combine the 15 tables with varying data into 5 new views showing all the merged infomration. I would imagine i would need to parse strings from the columns to pull out the relevant information.....Im just not sure how to proceed. After spending days looking through a refernce book i think I would need to create a separate view for each table and then using the join command merge the data into another view.

    Any help would be greatly appreaciated.

    Thanks

  2. #2
    Join Date
    Oct 2004
    Posts
    4
    Hi,

    trying the same here. I think the best method to come to a solution is to think from the direction of requirements, what the user really needs. Design Tables with Fields which contain the information the user needs. Next step is to design views which transform the source data in to destination data. I'm trying to do this in 2 further steps. First one is to integrate all data from all tables via on big join. Second one is to load data from that view to a fact/measure table and to dimension tables with artificial keys. Take a look at topics like Data Warehouse and OLAP.
    Hope this will help you a bit.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    sounds more like you need to normalize your database rather than create some sort of star-table. You main problem here is that some 'genious' decided to use duplicate tables for each vendor instead of ONE table with just a vendor code or something of that nature.

    now you are in a predicament because the data needs to be combined. The data should have been combined to begin with (IMO). Instead, now you have to create useless code to combine all the differently designed tables together.

    I say (since you are going through this whole thing anyway) you should forget the VIEWS and actually MIGRATE the data to redesigned, normalized tables.

    If you are going to go through all that work you might as well make it permanent (and correct) instead of using up processing power with all those views.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Oct 2004
    Posts
    4
    Sounds to me like the tables are normaized and he needs to merge them for analysing data. If not, forget my replay above.

Posting Permissions

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