DSXchange

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!

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'.

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.

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

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?"

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.

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,

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!

All of that is in my very first reply. Ray touched on it as well. Go back and reread those posts and let us know if any of that is still unclear.