Database Field Values That Look like Scientific Notation - Need to fix
Posted on 2008-10-28
We have a Firebird v1.5 database that lives under a manufacturing program. I believe this program is not storing numeric values correctly, but the software creator doesn't seem to think so, and I am breaking the user agreement by even looking at the raw data. Regardless, I have used Database Workbench v2.8 to regularly run SQL scripts to correct values that are created with far too many decimal places. It is hard to explain why this is necessary, but it screws with the reporting aspect of the software, this is another sticking point, but not one I will address here.
The full SQL script has about 20 lines that each correct a field in a table. This is what a typical line in the SQL script looks like (this syntax was provided by another EE member):
update ITEM set COST = cast (COST as decimal(15, 4));
Now lately, the SQL script has started to choke in one of the tables, and I believe it is because many of the fields contain erroneous values that looks something like:
-1.888522130117E296, this value should be 0.
That value isn't treated as a numeric, the above script ignores it, so gradually we seem to be getting more of these fields. Of course the actual values vary, but they all have seem to have the "E" character in the fourth position from the right. I'd always thought that indicated error, but am not sure.
So, here's my actual question:
Is there a line, or lines, that I can add to the SQL script that will change all these erroneous values to 0? Please provide an example of code. Also, if anyone know why these values may be getting created, please, do tell.