Page 1 of 2
string to decimal conversion
Posted: Sun Oct 11, 2009 12:21 am
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
Posted: Sun Oct 11, 2009 12:29 am
by sunitha_cts
VAR2 is (18,2)
Posted: Sun Oct 11, 2009 4:40 pm
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.
Posted: Tue Oct 13, 2009 8:39 am
by sunitha_cts
Hi Ray,
for var2 length 18 and scale 2.
Thanks
sunitha
Posted: Tue Oct 13, 2009 8:41 am
by sunitha_cts
HI,
Datatype is varchar
Thanks
Sunitha
Posted: Tue Oct 13, 2009 9:41 am
by chulett
Datatype of what is varchar? There's no scale for strings.
Posted: Sat Oct 17, 2009 2:35 am
by sunitha_cts
Hi,
Datatype of VAR2 used in the above code is varchar length 18 and scale 2.
Thanks
Sunitha
Posted: Sat Oct 17, 2009 6:53 am
by chulett
Again, a string doesn't have a scale. What happens if you remove the scale value?
Posted: Sun Oct 18, 2009 5:16 am
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
Posted: Sun Oct 18, 2009 5:23 am
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.
Posted: Sun Oct 18, 2009 5:43 am
by sunitha_cts
Hi Murthy,
It would work fine ,is their anyother solution for it for this issue.
Thanks
Sunitha
Posted: Sun Oct 18, 2009 5:53 am
by dr.murthy
what steps you have taken to resolve this issue could you let me know .
Posted: Sun Oct 18, 2009 6:57 am
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?
Posted: Tue Oct 20, 2009 11:36 am
by sunitha_cts
yes true
Posted: Tue Oct 20, 2009 11:58 am
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.