DSXchange

Hi Experts,

I have a requirement of joining two tables on a column value with Partial Match (Not exact key match).
Example:
First Table Data value: "Mathematica"
Second table Data value: "Mathematica 11.0 JDBC Support x64"
"Mathematica 11.0 JDBC Support x86"
"Wolfram Mathematica 12 (M-WIN-L 12.0.0 6175899)"
"Mathematica 9.0 JDBC Support x64"

In this case my job needs to join that 1 record with 4 records as "Mathematica" is available in all 4 records and output all 4 records.

I am able to implement the same logic using below SQL:

select a.Col_Name, a.Col_Name
from TABLE1 a
, TABLE2 b
where b.Col_Name like '%'+a.Col_Name+'%'

Is there any way to do same in Datastage without using LIKE operation in source query.

Thanks

Have you tried the Index() function in a Transformer stage?

You could use the Index() function - good idea - in the condition expression of a Lookup stage, or build the results of the Index() function converted to Boolean into the reference data set and a constant on the stream input to effect the lookup.

Thanks all for you valuable input.

I have tried using Index function, but to use that I need to bring both the columns in a single record, using a Cartesian Join.

Not true. Add an additional column to each input, and lookup/join on that. That column contains the result of your Index() function.