Matching the records
Posted: Thu Jan 27, 2011 11:32 pm
Hi All,
I am having some 1 millions of records(Source) having 4 fields and I have another reference table of 3 fields containing some 6-7K of records.
The structure of Source table is something like:
Field1, Field2, Field3, Field4
004960239674199,4921316782,SI025308,LNAZHADE
0034918382252,5939793841,ZHALNADE,SI017501
and so on........
And the structure of reference table is :
Field1, Field2, Field3
ABC, Test1, 49679
ABC, Test2, 493
ABC, Test3, 492
.
.
.
BCD, Test, 482
and so on.....
My requirment is that i need to match the field2 of input source with field3 of reference table in the way that Field3 of reference table exists somewhere in the begining of the field2 of source input. In the above example the output shul be like :
004960239674199,4921316782,SI025308,LNAZHADE,ABC, Test3
0034918382252,5939793841,ZHALNADE,SI017501,,
I have got this logic working in perl but because the performance is not as per requriment i wanted to perform same thing in datastage.
So can anyone suggest how to achieve this in datastage, any help is appeciated.
I hope i am clear in explaining the requirment.
I am having some 1 millions of records(Source) having 4 fields and I have another reference table of 3 fields containing some 6-7K of records.
The structure of Source table is something like:
Field1, Field2, Field3, Field4
004960239674199,4921316782,SI025308,LNAZHADE
0034918382252,5939793841,ZHALNADE,SI017501
and so on........
And the structure of reference table is :
Field1, Field2, Field3
ABC, Test1, 49679
ABC, Test2, 493
ABC, Test3, 492
.
.
.
BCD, Test, 482
and so on.....
My requirment is that i need to match the field2 of input source with field3 of reference table in the way that Field3 of reference table exists somewhere in the begining of the field2 of source input. In the above example the output shul be like :
004960239674199,4921316782,SI025308,LNAZHADE,ABC, Test3
0034918382252,5939793841,ZHALNADE,SI017501,,
I have got this logic working in perl but because the performance is not as per requriment i wanted to perform same thing in datastage.
So can anyone suggest how to achieve this in datastage, any help is appeciated.
I hope i am clear in explaining the requirment.