On 11/4/07, Karen Stevenson <karen@elderweb.com> wrote:

I'm working on the CCK D6 port and I need help coming up with the SQL to convert floats stored in scientific notation back to decimals.

Up to now, CCK has used a float field to store the 'decimal' field type, which works fine if the numbers are not too large, but ends up stored in scientific notation if they are. This has been a known CCK problem for a long time but fixing it before now would have involved re-writing some of the most complex code in CCK, so it hasn't gotten done.

In D6 we now have a real decimal field to use, which is great, but I need to write an D6 update that will take the data sometimes stored in scientific notation and sometimes as decimal values in those float fields, and migrate it to decimal fields without losing any data, and I badly need help finding the right SQL to do this update.

You can easily create a test bed by creating a table with a float field and a decimal field. Then store some small and some very large integers and decimals, in the float field and try to copy those stored values from the float field back to the decimal field without losing or corrupting any data.

And of course we have to do it in a way that works in both MYSQL and Postgres :)

Thanks!

Karen

I am a bit puzzled about the possibility of a generic solution covering decimal columns of any length. A conversion with a simple UPDATE table SET decimal_col = float_col would work fine, but only if the (digits, decimals) of the decimal column are large enough to hold the number, otherwise the results would be wrong. Are you going to use something big, such as decimal(64,32)? Or are you looking for a conversion which preserves the most significant digits up to some point? How many decimal digits do you have in mind? Perhaps I don't understand this correctly, but storing the values as decimals looks like trouble to me.