Lookup with a Like Operator

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
tradersjoe57
Premium Member
Premium Member
Posts: 13
Joined: Mon Oct 24, 2016 7:03 am

Lookup with a Like Operator

Post by tradersjoe57 »

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
Premium Member
Premium Member
Posts: 1021
Joined: Sun Mar 03, 2002 6:01 pm
Location: Tampa, FL

Post by Mike »

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
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

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.
tradersjoe57
Premium Member
Premium Member
Posts: 13
Joined: Mon Oct 24, 2016 7:03 am

Post by tradersjoe57 »

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.
rameshrr3
Premium Member
Premium Member
Posts: 609
Joined: Mon May 10, 2004 3:32 am
Location: BRENTWOOD, TN

Post by rameshrr3 »

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 .
Post Reply