String to decimal, unable to get proper data
Moderators: chulett, rschirm, roy
String to decimal, unable to get proper data
Hi,
We are doing data conversion from varchar to decimal, but instead of data ,we are getting value '000.0000'.
source data like ' 100.0000'(including space).
My experiments
I tried with the below options.
strepwhitespace(Lnk_out_Unload_File.DISTR_PERC)
trim(Lnk_out_Unload_File.DISTR_PERC,' ','A')
while using the trim function and putting target as varchar, data will come without space like '100.0000' --while testing in peek stage
while using the trim function and putting target as decimal, data will come without space like ' 000.0000' --while testing in peek stage
My final output should be '100.0000'
We are doing data conversion from varchar to decimal, but instead of data ,we are getting value '000.0000'.
source data like ' 100.0000'(including space).
My experiments
I tried with the below options.
strepwhitespace(Lnk_out_Unload_File.DISTR_PERC)
trim(Lnk_out_Unload_File.DISTR_PERC,' ','A')
while using the trim function and putting target as varchar, data will come without space like '100.0000' --while testing in peek stage
while using the trim function and putting target as decimal, data will come without space like ' 000.0000' --while testing in peek stage
My final output should be '100.0000'
Last edited by deesh on Fri Mar 17, 2017 7:53 am, edited 3 times in total.
So... color me a bit confused. Sometimes you show "000.0000" and sometimes "100.0000". And sometimes you say "without space" and yet you show one. Is that on purpose or are one/some of them typos? Or is your trim/conversion fun question solely around it removing that "1" from your source?
Regardless, can you clean up your examples please so they properly reflect what you are trying to get help with? You can always edit your own posts after the fact, by the way.
Regardless, can you clean up your examples please so they properly reflect what you are trying to get help with? You can always edit your own posts after the fact, by the way.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: String to decimal, unable to get proper data
I have modify the statement, is above one is ok (no confused) --craig
Re: String to decimal, unable to get proper data
deesh wrote:while using the trim function and putting target as decimal, data will come without space like ' 000.0000' --while testing in peek stage
Says "without space" and yet still has one, assuming you mean "with" there as that's how the decimal data type works - that is where the sign goes. And does it really remove the "1"?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Basically... I'm trying to clarify your exact issue is with improper data. Does it actually change the "100" to "000" or is it simply the space at the front as a decimal in the peek stage? Because the latter is not an issue at all but simply How It Works.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Thanks.
What happens if you simply "trim" (with no options) the string inside the string to decimal? Meaning:
I'd also be curious how your decimal target is defined, the precision and scale settings.
What happens if you simply "trim" (with no options) the string inside the string to decimal? Meaning:
Code: Select all
StringToDecimal(Trim(Lnk_out_Unload_File.DISTR_PERC))
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Help us help you. I feel like I'm a dentist here, pulling teeth. One.... at... a... time.
You need to clarify what the data type is for each of these results since they've seemed to fluctuat over the course of this discussion. If a string then the size, if a decimal then precision and scale. And where you are seeing them, peek stage still, flat file, what? I would rather have too much information where I can find the nuggets that help rather than too little where the nuggets have yet to arrive.
You need to clarify what the data type is for each of these results since they've seemed to fluctuat over the course of this discussion. If a string then the size, if a decimal then precision and scale. And where you are seeing them, peek stage still, flat file, what? I would rather have too much information where I can find the nuggets that help rather than too little where the nuggets have yet to arrive.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
I for one am out of ideas as this is making no sense to me. I suspect we're missing a key piece of information, that or you've found a bug in your very old version of DataStage. Are you 'current' on fix packs for your release? Have you opened a support case? I assume it is no longer being actively supported but don't imagine that also means they won't help.
Anyone else will to throw something against this wall, see if it sticks?
Anyone else will to throw something against this wall, see if it sticks?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
I knew we were missing a key piece of information. And you never mentioned your source was a file which would have been helpful to know.
Next time either transfer your DOS files correctly to UNIX (so they drop the ^M aka x0D and /r) or simply tell DataStage to use the proper record delimiter for them. I'm assuming this was the last field in the file, yes?
Next time either transfer your DOS files correctly to UNIX (so they drop the ^M aka x0D and /r) or simply tell DataStage to use the proper record delimiter for them. I'm assuming this was the last field in the file, yes?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers