Matching the records

[sourabhverma]

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.

[ray.wurlod]

Server job. ODBC stage using user-defined SQL on the reference input to a Transformer stage. Use the LIKE operator in the user-defined SQL.

[sourabhverma]

Hi Ray,

Thanks for the reply, the problem with LIKE operator is that data can have any pattern and i don`t know about the actual string.

[Shruthi]

Take the count of Field 3 in reference link and put it in one column, say A1.

In Transformer, extract srcA[1,A1] - extract the required substring and then do a lookup again to do comparsion.

Hope this works

[sourabhverma]

I got it done done by having certain number of lookup (rejects link). I had input source & created required numbers of copies of reference data & matched in the respective lookup.