string to decimal conversion

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

sunitha_cts
Participant
Posts: 98
Joined: Thu Feb 05, 2009 1:14 am
Location: visakhapatnam
Contact:

string to decimal conversion

Post by sunitha_cts »

Hi,


HI,

I have a varchar(18) value say 17826.12 coming from source now since this value is greater than 10000.00 the target value is losing its least significant values my target value is coming as 17826.1,the values below than 10000.00 coming from source is absolutely fine.

my code is as follows

String to Decimal(var1)/100-->VAR2

if len(VAR2)='0' then '0' else DecimaltoString(VAR2,"suppress_zero")
CAN ANYONE SORT OUT THIS ISSUE FOR ME.


Thanks
Sunitha
sunitha_cts
Participant
Posts: 98
Joined: Thu Feb 05, 2009 1:14 am
Location: visakhapatnam
Contact:

Post by sunitha_cts »

VAR2 is (18,2)
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

What you are describing makes perfect sense, assuming that VAR2 has datatype Decimal(18,2) - you left out the word "Decimal".

You are dividing by 100, to produce a decimal number with a scale of 4, but then loading it into a decimal data type with a scale of only 2. Therefore the two least significant digits have to disappear. Whether the result is rounded or truncated depends on your rtype argument.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
sunitha_cts
Participant
Posts: 98
Joined: Thu Feb 05, 2009 1:14 am
Location: visakhapatnam
Contact:

Post by sunitha_cts »

Hi Ray,

for var2 length 18 and scale 2.

Thanks
sunitha
sunitha_cts
Participant
Posts: 98
Joined: Thu Feb 05, 2009 1:14 am
Location: visakhapatnam
Contact:

Post by sunitha_cts »

HI,

Datatype is varchar

Thanks
Sunitha
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Datatype of what is varchar? There's no scale for strings. :?
-craig

"You can never have too many knives" -- Logan Nine Fingers
sunitha_cts
Participant
Posts: 98
Joined: Thu Feb 05, 2009 1:14 am
Location: visakhapatnam
Contact:

Post by sunitha_cts »

Hi,

Datatype of VAR2 used in the above code is varchar length 18 and scale 2.

Thanks
Sunitha
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Again, a string doesn't have a scale. What happens if you remove the scale value?
-craig

"You can never have too many knives" -- Logan Nine Fingers
sunitha_cts
Participant
Posts: 98
Joined: Thu Feb 05, 2009 1:14 am
Location: visakhapatnam
Contact:

Post by sunitha_cts »

HI,

The code is being deployed into production with length having scale for varchar by someone else and it is working fine but as i posted i need to see that the least significant values are not removed,Can you help me fix the issue for data accuracy.

Thanks
Sunitha
dr.murthy
Participant
Posts: 224
Joined: Sun Dec 07, 2008 8:47 am
Location: delhi

Post by dr.murthy »

just you can change the datatype for the field var2 is decimal(16,2) where 2 is scale, i hope it might works in your situation.
D.N .MURTHY
sunitha_cts
Participant
Posts: 98
Joined: Thu Feb 05, 2009 1:14 am
Location: visakhapatnam
Contact:

Post by sunitha_cts »

Hi Murthy,

It would work fine ,is their anyother solution for it for this issue.

Thanks
Sunitha
dr.murthy
Participant
Posts: 224
Joined: Sun Dec 07, 2008 8:47 am
Location: delhi

Post by dr.murthy »

what steps you have taken to resolve this issue could you let me know .
D.N .MURTHY
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

sunitha_cts wrote:and it is working fine but as i posted i need to see that the least significant values are not removed
Then it's not exactly "working fine", is it?
-craig

"You can never have too many knives" -- Logan Nine Fingers
sunitha_cts
Participant
Posts: 98
Joined: Thu Feb 05, 2009 1:14 am
Location: visakhapatnam
Contact:

Post by sunitha_cts »

yes true
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Did you make any changes, have any luck with this? I thought it was marked as 'resolved' previously but it doesn't seem to be any longer. :?
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply