Solved

Database Field Values That Look like Scientific Notation - Need to fix

Posted on 2008-10-28
5
413 Views
Last Modified: 2013-12-09
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
 
0
Comment
Question by:boris711
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 22825544
The E means exponent.  This is a computer version of scientific notation.  I'm not sure about your other questions though.
0
 
LVL 19

Accepted Solution

by:
NickUpson earned 450 total points
ID: 22825562
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


0
 
LVL 18

Assisted Solution

by:UnifiedIS
UnifiedIS earned 50 total points
ID: 22825576
I think that -1.888522130117E296 = -1.888522130117 * 10 raised to the 296th power which generates an arithmetic overflow when I try to cast it as decimal(15,4).
0
 
LVL 1

Author Comment

by:boris711
ID: 22826003
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.
0
 
LVL 1

Author Closing Comment

by:boris711
ID: 31510905
Nick:
This appears to be working as I'd hoped.
update ITEM set COST = 0 where cost < 0.000001 (or whatever cutoff point you want to use)

Thank you, again
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Read about achieving the basic levels of HRIS security in the workplace.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now