Multiple tables loaded into single target table

[ragu]

Hi,

Need some help with this requirement

I have 3 oracle tables ( which has a sinlge pseudo key column meaning there can be duplicates) with raw data directly from Operational data

3 tables have different metadata and there is a single ID field which is suppose to be same in all the three tables

these three table have to be loaded into a single target table

For example,

Table A has KeyField, and 4 other fields
Table B has KeyField and 3 other fields
Table C has KeyFilef and 3 other fields

The taraget table should have Surrogate key(Auto-generated Number), KeyField, 4 fields from A, 3 fields from B and 3 fields from C

This is not the end to this, there cannot be duplicates -- meaning if there are 2 records with all the columns exactly same, then the second record can be rejected. If any one column differsm it has to be inserted as a new column with a new flag and numbering each new data with same KeyField

It sounds little complicated but if any of you came across this situation, help me with this

One of the way that I am thinking is --- loading these source tables into source files and using merge stage to get one single file with all the column and map it to the target with hash lookup against the target table

Am all confused, help me with this

Ragu

[loveojha2]

One of the way that I am thinking is --- loading these source tables into source files and using merge stage to get one single file with all the column and map it to the target with hash lookup against the target table

Or take any table A, B or C as a source table,

The rest of the two tables would be used as lookup (on key field).

Get the nonkey fields from the source table and the lookup table into the target on each successful match.

And finally as you said, target lookup.

Hope this will help you.

[ArndW]

How about using Occam's Razor and going for the option to join all 3 tables in Oracle and passing that to DataStage for further processing?

[loveojha2]

Moreover use intermediate Hashed File for the lookup.

[rkdatastage]

The problem which u had defined is similar to scd type 2 method, hope u can resolve it , i didn't find where u had strucked up exactly so that i can give my inputs

RK

[victorbos]

I would do most of this in Oracle.
In a single query you can get rid of duplicate records, and create a single table based on the key column, like:

Code: Select all

select *
  from 
    (select distinct <columns> from a) a,
    (select distinct <columns> from b) b,
    (select distinct <columns> from c) c
  where a.key = b.key and a.key = c.key

To generate the surrogate key you can use an oracle-sequence.

hope this helps,

Victor