DSXchange

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!

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

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.

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.

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 .