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.
Many TIA!
Jeff
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
update ITEM set COST = 0 where cost < 0.000001 (or whatever cutoff point you want to use)
The fields do contain numbers, the 'E' is db workbench displaying a very small number, too small to be held as 15.4, hence your problem
you could try using
see what you get from (and vary the fixed numbers)
select COST, round(COST, 5 ), floor(COST, 5 ) from item
Thank you both for your quick responses. Nick I will try your first suggestion.
I seem to remember that someone told me that Firebird SQL v1.5 does not have a Round function. And, I have not been able to find anything in the Firebird documentation that supports the existence of that function either. Which is why we had to go with the script functions and syntax in our example.
I get the Math now, too.
Business Accounts
Answer for Membership
by: UnifiedISPosted on 2008-10-28 at 12:57:13ID: 22825544
The E means exponent. This is a computer version of scientific notation. I'm not sure about your other questions though.