Why would a SQL cast fix the issue? Casting a numeric(10,2) to a numeric(10,2) would not solve anything, which means that the value you are casting to numeric must be of a different data type, likely float or varchar. Floating point numbers often display in scientific notation. The numeric value is possibly being automatically converted into a floating point number by its use in a math function.
It might help to see the SQL with the cast function that is fixing the issue. SELECT numeric_col FROM table should not require any further casting and you would not see the problem you are seeing. However, if you do something like this SELECT '000' + numeric_col + 1.2E-20 / 1.234 + 'cat' - 'dog' as newval FROM table then you are no longer dealing with a numeric, even though the source number might have started out as one. I'm still not clear on what the problem is. As a reminder, try to create a simplified example that isolates the problem being seen, away from any surrounding code. During the exercise of creating the simplified example, testing each increasingly complex iteration of the code, the source of the error will likely be revealed. If you were hoping for a quick answer to your question, I don't have one. Perhaps someone else has seen this issue when upgrading CF. -Mike Chabot On Wed, Feb 16, 2011 at 1:43 PM, James Skemp <[email protected]> wrote: > > >It occurred to me that I should first ask if this issue is causing any > >problems, or if it is only an unexpected way to format a number? For > example > >0.00 = 0E-8. They are both the same number regardless of how they are > >displayed. > > Unfortunately, it is causing an issue in this particular instance (and is > in fact why we had a stack trace to see that 0E-8 was being returned) :) > > It's legacy code, so with some work we could certainly resolve the issue, > but, as a legacy *third-party* solution, my concern is it's going to cause > issues in other parts of the system. Assuming this is expected behavior in > CF8/we haven't flipped the appropriate 'switch.' > > > >For clarification, which number has the unexpected formatting, the value > >pulled directly out of a database table and placed in the cfquery record > >set, the variable that is being assigned the value, or both? > > Probably the former - ColdFusion's interpretation of the value from SQL / > the value in the cfquery record set, since the resolution in this particular > case is to cast the returned value to a particular type in the procedure. > > But, another way to resolve the issue is to check the formatting when > setting the variable, so I suppose one could argue both. The immediate > problem is because of the latter, but the root cause is the former. > > In short, "0E-8" is populated in the proc's cfquery result set, a variable > is being set with "0E-8", and another query is failing because it doesn't > care for "0E-8" being passed. > > Hopefully that last bit didn't muddy the waters ... :) > > ~James > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342348 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
