problem: cfquery returning scientific notation

[satorisurfer]

I am writing a CF application that queries a Centura SQLBase DB--not my DB of choice, its an interface partner's DB! Some of the fields in the SQLBase DB are of type FLOAT, which for some reason CF is returning in scientific notation (i.e. 1.23653E7) rather than as a whole number. Additionally, CF is losing precision, so I can't even run a function against the result to convert it back to the original number.

Any ideas on how I can make CF not convert the number to scientific notation, or at least preserve the original precision?

[cmhampton]

That's not so much a coldfusion problem as it is an SQL "feature". Floats are not exact representations of numbers, but approximates. Here's an explanation: http://msdn.microsoft.com/en-us/library/ms173773.aspx

In your query, you should be able to CAST the float into another datatype, such as a decimal and that should solve your problem.

For example:

DECLARE @float float;
DECLARE @casted decimal(18,10);
SET @float = 10000000 / 7.65;

PRINT @float

SET @casted = CAST(@float AS decimal(18,10))

PRINT @casted

This is MSSQL, but I would imagine the Centura syntax would be similar. It should return:

1.30719e+006
1307189.5424830001

The first number is the approximate representation from the float, and the second is the exact value in decimal.

I hope that helps.