Conversion in Transformer

Rajesh Jale · Post by **Rajesh Jale** » Wed Feb 18, 2015 2:09 am

Hi,

I have source column Code char(9) which contains code in both forms numbers and letters like 161,ADG.
I'm using transformer where i have condition that when code is 112 then current timestamp else column2

Condition : if code = 161 then CurrentTimestamp() else column2

But im facing a problem when using above condition, its unable to match the number since its defined as char. May I know whats the best way to convert char, so that it can match both numbers and letters in the condition.

Can someone help me with this.

Regards,
Rajesh

qt_ky · Post by **qt_ky** » Wed Feb 18, 2015 5:59 am

Have you tried use the Index function?

http://www-01.ibm.com/support/knowledge ... tions.html

chulett · Post by **chulett** » Wed Feb 18, 2015 8:20 am

It's not unable to match it, it should be perfectly capable of doing that but you're right the CHAR part does make it a little trickier. I would suggest a couple of things, one or both may help.

Test for the character value "161" rather than the number...i.e. use quotes so it does a string match rather than feel a need to convert one side or the other. And since the CHAR carries trailing spaces you may need to trim the field in the comparison but I'm thinking just that first step may solve things for you.

vibwipro · Post by **vibwipro** » Wed Feb 18, 2015 8:56 am

When ever data type char is defined then it will Pad spaces to existing data to make compatible with length of field.

So use Substring function to cut actual data characters form a space padded string.

Rajesh Jale · Post by **Rajesh Jale** » Thu Feb 19, 2015 4:30 am

Trim works, also tried using double quotes like "161" but didnt help me. However trim works perfectly. Thanks Craig for solution.