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



Group memberships:
Premium Members

Joined: 24 Oct 2016
Posts: 11

Points: 161

Post Posted: Tue Apr 18, 2017 3:05 pm Reply with quote    Back to top    

DataStage® Release: 8x
Job Type: Parallel
OS: Unix
I have a Input file with a Key Column which should be looked up into a table which sometimes might have a starting letter or a full string. So, basically I have to use LIKE for lookup which we don't have in Datastage.

Any thoughts on how to accomplish this.

One way is to load my input data into another table and then join in Datastage using a Query. But loading input data into the same DB seems to be not possible. So trying to find other options of how to do it.

Any help is much appreciated!
Mike



Group memberships:
Premium Members

Joined: 03 Mar 2002
Posts: 1010
Location: Omaha, NE
Points: 6513

Post Posted: Tue Apr 18, 2017 4:26 pm Reply with quote    Back to top    

Is it always a single starting letter?

If yes, do 2 lookups: 1) full string, and 2) first character.

You could make the second lookup conditional on not finding a match with the full string.

Mike
Rate this response:  
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 252

Points: 2537

Post Posted: Wed Apr 19, 2017 6:58 am Reply with quote    Back to top    

you could also create a new column with some logic that produced the expected lookup match value -- here, that sounds like conditionally dropping the first character -- and looking up off that, then discard the column.
Rate this response:  
tradersjoe57



Group memberships:
Premium Members

Joined: 24 Oct 2016
Posts: 11

Points: 161

Post Posted: Wed Apr 19, 2017 7:18 am Reply with quote    Back to top    

Thanks for the answer Mike. Actually, it is a 4 character column. And it can be 1 character or 2 character ..or 4 character. So, I need to do look up based on what I have in the Table.

So, I am thinking starting a lookup with 4 character and then the rejected one goes to another lookup with 3 character lookup from the table and so on.
Rate this response:  
rameshrr3



Group memberships:
Premium Members

Joined: 10 May 2004
Posts: 597
Location: BRENTWOOD, TN
Points: 6741

Post Posted: Wed Apr 19, 2017 10:01 am Reply with quote    Back to top    

I think you can use the same lookup stage - the reference link can be copied into 4 different ref links using a copy stage, and then use the approrpriate condition/scenario in each , drag all the result columns from the ref links to a subsequent transformer stage to do survivor scenario evaluation.
PS : i'm not sure if it will help - But i had an impression that the Filter Stage supports a "LIKE" operator .

IF all else fails, do a full outer join if your volumes are tolerable , the Basic Transoformer stage still has the valuable "Matches" operator which has wildcard feature . The Basic transformer is viable if you have SMP, its behavior on grid/MPP is unpredicatable .
Rate this response:  
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