Use "LIKE" functionality in transformer

[vinaymanchinila]

Hi,
I am converting ETL work built in SQL to DataStage. I have a statement which uses case statement with the "LIKE" command and I donot have the temp table, so how do I get the functionality of the LIKE command in DS transformer.

For example I have the following command

SELECT CUSTOMER,
PRODUCT,
CASE when DESC like '%T%' and FAMILY like '%TRY%' and CUSTOMER IN ('X1','X2','X3') then 'CASE1'
when LOCATION in ('LAX','CMH') then 'PRO'

Thanks,

[mahveen]

Hi,

In the transformer, we can always use the substring function to incorporate 'like' functionality.

The other way could be using case statement in sql query and using an indicator field if it satisfies the like condition. and using it in the transformer.

[vinaymanchinila]

Hi,

I donot have the table, bascially what is being done (Case statement ) has to be implemented over a file.
Thanks,

[kaps]

I believe you can use If then else statement in your transformer instead of Case statement.

Thanks

[ray.wurlod]

The Transformer stage uses non-SQL pattern matching.

Read about pattern matching in the on-line help or the DataStage BASIC manual.

Example:
inlink.Desc Matches "...T..." (the three dots is a wildcard matching any number of characters)
inlink.ProdCode Matches "2A3N..." (ProdCode must begin with two alphabetic characters followed by three numeric characters)