Join on subset of data

[_chamak]

Hi I have a requirement to join a subset of data. Below is the design

Flat file
|
|
Oracle------>LKP------>DS

Example I have to join on ID (present in oracle and Flat File)
Values for ID in Oracle Values in Flat file
123 3
456 5

we need to get the two rwos in the output, Subset of data in falt file should match with the data in oracle.

Note: Above was an example and I need to implement for large number of records.

Will I be able to achieve this in Datastage? if its a table in the refrence then I can use substr and instr to achive my requirement.

[yugee]

If I understand your requirement, you want to use some function similar to LIKE in Oracle. If substring of your oracle key is present in sequential file key, you want to output those records.

In that case (you want only the matched rows), why don't you interchange your source and reference.

have source as seqfile, and use sparse lookup using LIKE function in WHERE clause. If you are extracting specific rows from oracle, you can mention that as well in your WHERE (example - created > 'timestamp)

----of course this solution depends on number of inputs in seqfile and your oracle table, but this is the solution I could think of immediately...

[ray.wurlod]

Or is it that you wish to match against the final digit of the first ID?
Your requirement is not clearly stated.