String to decimal, unable to get proper data

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
deesh
Participant
Posts: 193
Joined: Mon Oct 08, 2007 2:57 am

String to decimal, unable to get proper data

Post by deesh »

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'
Last edited by deesh on Fri Mar 17, 2017 7:53 am, edited 3 times in total.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
deesh
Participant
Posts: 193
Joined: Mon Oct 08, 2007 2:57 am

Re: String to decimal, unable to get proper data

Post by deesh »

I have modify the statement, is above one is ok (no confused) --craig
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Re: String to decimal, unable to get proper data

Post by chulett »

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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
deesh
Participant
Posts: 193
Joined: Mon Oct 08, 2007 2:57 am

Post by deesh »

Yes converting the data ' 100.0' to '0.0' while doing string to decimal
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Thanks.

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))
I'd also be curious how your decimal target is defined, the precision and scale settings.
-craig

"You can never have too many knives" -- Logan Nine Fingers
deesh
Participant
Posts: 193
Joined: Mon Oct 08, 2007 2:57 am

Post by deesh »

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'
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
deesh
Participant
Posts: 193
Joined: Mon Oct 08, 2007 2:57 am

Post by deesh »

Yes, seeing the results in peek stage.
Source data type string and target data type decimal(15,10)
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

What version of DataStage are you using?
Choose a job you love, and you will never have to work a day in your life. - Confucius
deesh
Participant
Posts: 193
Joined: Mon Oct 08, 2007 2:57 am

Post by deesh »

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'
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.

:idea: 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
Post Reply