Converting StringToDecimal and back DecimalToString in a single transformer stage:
I have a job where I need to perform data type conversion in a single transformer stage. I have a Varchar2(25) field which needs to be converted to Decimal (20,1) and then back to Varchar2(25).The challenge over here is I need to do all this in single transformer stage to avoid stages in the job.
When I am doing it in multiple stages, I get the below error. This is just a snapshot of the long error:
##I IIS-DSEE-TFCN-00009 09:09:49(001) <main_program> The timezone environment variable TZ is currently not set in your environment which can lead to significant performance degradation. It is recommended that you set TZ=:/etc/localtime in your environment.
Welcome. First let's get you in the proper forum. There, all done.
Now, there's no need to do this in two transformers or even two steps unless you need to store the intermediate string value... do you? Otherwise you just 'inline' the two function calls, put the first inside the second. To help with your error we'd need to see the actual syntax you are using. That and some examples of input and output needed / expected since I'm curious why this data needs to go 'there and back again'.
-craig
"You can never have too many knives" -- Logan Nine Fingers
I ran into the same message about the TZ variable a while ago, we just needed to have the env. variable defined, the post below gave me insight about it.
Vaibhav, what operating system is your DataStage server on? You've marked the post as Windows but the error looks like it comes from a UNIX system and the advice in Andy's link is specific to RedHat Linux, hence the question.
-craig
"You can never have too many knives" -- Logan Nine Fingers
Thanks all for your prompt response.
My second issue got resolved. I was using NullToZero(StringToDecimal(INPUT_COL)) which was giving me timezone error.
Craig: My bad, I have Unix as my OS.
Ray: For your question regarding "why?" - I don't want to complicate my job by introducing two transformer stages just to apply this logic considering performance standpoint.
Here's my data and requirement - My input is "2000.21" which is Varchar2(25) as its datatype. This first needs to be converted to Decimal (20,1) i.e. 2000.2 and this value should again be translated into Varchar2(25) and loaded into the target table.
So... still no joy on your conversion? If so, as a first step please post the syntax you are using. We'd also need to know what your decimal conversion rules are - rounding, truncating? For example if your source string was "2000.28" would the end result still be "2000.2" or would it be "2000.3"?
-craig
"You can never have too many knives" -- Logan Nine Fingers
It should get rounded to "2000.3". As of now, I am using two transformer stages to achieve my end result.
Step 1) Converting varchar2(25) to Decimal(20,1) using syntax: StringToDecimal(2000.29).
Step 2) Convering Decimal(20,1) to varchar2(25) using syntax:
DecimalToString(2000.3,"suppress_zero")
Please let me know, if I can achieve this operation in a single stage.
