Converting StringToDecimal and back DecimalToString
Moderators: chulett, rschirm, roy
Converting StringToDecimal and back DecimalToString
Hi All,
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.
Appreciate your help!
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.
Appreciate your help!
Vaibhav
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'.
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
"You can never have too many knives" -- Logan Nine Fingers
Re: Converting StringToDecimal and back DecimalToString
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.
viewtopic.php?t=154559
I don't think this message relates to the conversions you are performing.
viewtopic.php?t=154559
I don't think this message relates to the conversions you are performing.
On a secondary note - if you are getting that error, you need to do what it says...
http://www-01.ibm.com/support/docview.w ... wg21598208
http://www-01.ibm.com/support/docview.w ... wg21598208
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
To add to what Craig said, if you need the intermediate value, park it in a stage variable.
Can you tell us why you have to avoid stages in the job? Not just because "they" told you to, but their actual technical reason behind that directive.
Never be afraid to ask "why?"
Can you tell us why you have to avoid stages in the job? Not just because "they" told you to, but their actual technical reason behind that directive.
Never be afraid to ask "why?"
Last edited by ray.wurlod on Thu Feb 11, 2016 8:50 pm, edited 2 times in total.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.
Vaibhav
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
"You can never have too many knives" -- Logan Nine Fingers
Craig,
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.
Thanks!
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.
Thanks!
Vaibhav