DSXchange

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'

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.

I have modify the statement, is above one is ok (no confused) --craig

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

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.

Yes converting the data ' 100.0' to '0.0' while doing string to decimal

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.

Please find below what are the option I tried.

Trim(column,' ','A')-- result '0.0'
String to decimal(trim(column))-- result '0.0'
Field(column'.','1') -- result ' 00100'
Field(column,'.','1'):'.':Field(column,'.','2')- result '0.0'

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.

Yes, seeing the results in peek stage.
Source data type string and target data type decimal(15,10)

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?

What version of DataStage are you using?

I found the issue i am getting the control M char for that field and used below logic to remove the control and removing first three header rows in the file.

used External source stage

tail -n +4 #FileDirectories.SourceFiles#/File*.txt | tr -d '\r'

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?