labkey sql and converting numeric value to string

LabKey Support Forum (Inactive)
labkey sql and converting numeric value to string Ben Bimber  2010-10-08 09:09
Status: Closed
 
i have something like this in sql:

t1.volume || ' ' || t1.vunits as volume,

t1.volume is a numeric column. it's returning:
0.59999999999999998 ml

instead of the much nicer and expected:
0.6 ml

the value of that column is actually 0.6. any idea why the process of converting to a string does that? can i avoid it? unfortunately this does not fix it:

round(t1.volume, 2) || ' ' || t1.vunits as volume,

thanks for the help.
 
 
Matthew Bellew responded:  2010-10-08 10:59
This is a common problem when dealing with floating point data. Even relatively small decimal numbers can't be represented exactly, and different systems handle string conversion differently.

Try this

CONVERT(t1.volume, NUMERIC) || ' ' || t1.vunits as volume