DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
dwh_user



Group memberships:
Premium Members

Joined: 18 Oct 2018
Posts: 13
Location: Sydney
Points: 163

Post Posted: Wed Jul 24, 2019 1:07 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Windows
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



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 2878
Location: USA
Points: 21847

Post Posted: Wed Jul 24, 2019 7:39 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54546
Location: Sydney, Australia
Points: 295766

Post Posted: Fri Jul 26, 2019 6:59 pm Reply with quote    Back to top    

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 cons ...

_________________
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Rate this response:  
Not yet rated
dwh_user



Group memberships:
Premium Members

Joined: 18 Oct 2018
Posts: 13
Location: Sydney
Points: 163

Post Posted: Wed Jul 31, 2019 1:53 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54546
Location: Sydney, Australia
Points: 295766

Post Posted: Wed Jul 31, 2019 7:29 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours