loading History data

[sri75]

HI All,

I have to load history data into person dimension table from 3 sources.

Data is like this

table1

emplid effdt name
xxxx 01/jan/2001 abc
xxxx 06/jan/2001 abcd

emplid and effdt are key columns


table2

emplid effdt marital_status
xxxx 01/jan/2001 U
xxxx 04/jan/2001 M

emplid and effdt are key fields

in the target table data should be like this

sid emplid effdt name marital_status

1 xxxx 01/jan/2001 abc U
2 xxxx 04/jan/2001 abc M
3 xxxx 06/jan/2001 abcd M

in the target table SID is the key


Here I can't load the data based on just emplid . the effective dates are all different.

Can you please advice me how to develop ETL job or query

Thanks

[ray.wurlod]

Three sources? Table1 and Table2? Is this some new field of mathematics?

You seem to have a simple full outer join requirement. If the tables are all in the same database perform the join there.

Otherwise search DSXchange for techniques for performing a full outer join in a server job.

[sri75]

Hi Ray ,

thanks for your reply.

Sorry for the confusion.Actually I am getting data from more then 2 tables.
right now I have 4 tables but in future it may increase.I think I can't use the full outer join on multiple tables with the simple query.Can you please give me the idea how to implement this logic in datastage or query


Thanks

[chulett]

If what you need is a full outer join, then you'll need to figure out how to do it, simple or not. As noted, it certainly looks like that's all you need to do - all except for the 'sid' part. Where does that come from, another table or do you need to generate it during the run?

If you're uncertain about how to develop the sql you'll need, regardless of its full-outer-join-ness or not, surely there must be someone where you work who knows your data and can help you with this - DBA? Analyst? Fellow developer?

[sri75]

Craig,

Thanks for the reply

When I did full outer join
I am getting 4 records instead of 3

This is the query I wrote

SELECT A.EMPLID,B.EMPLID,A.EFFDT,A.NAME,B.MARITAL_STATUS
FROM TABLE1 A
full outer join TABLE B on
A.emplid=b.emplid where B.EMPLID='XXXX' AND A.EMPLID='XXXX'

I think UNOIN will be solution for my problem.I am going to try with UNIOn and will see

Thanks