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
Multiple tables loaded into single target table
Moderators: chulett, rschirm, roy
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.
Success consists of getting up just one more time than you fall.
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?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 107
- Joined: Wed Sep 29, 2004 10:15 am
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:
To generate the surrogate key you can use an oracle-sequence.
hope this helps,
Victor
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
hope this helps,
Victor