Need to implement logic to apply join with PARTIAL match key

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
dwh_user
Premium Member
Premium Member
Posts: 14
Joined: Thu Oct 18, 2018 8:08 pm
Location: Sydney

Need to implement logic to apply join with PARTIAL match key

Post by dwh_user »

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
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Have you tried the Index() function in a Transformer stage?
Choose a job you love, and you will never have to work a day in your life. - Confucius
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
dwh_user
Premium Member
Premium Member
Posts: 14
Joined: Thu Oct 18, 2018 8:08 pm
Location: Sydney

Post by dwh_user »

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.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Not true. Add an additional column to each input, and lookup/join on that. That column contains the result of your Index() function.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply