Solved

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

Posted on 2008-10-28
5
425 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Select Query help 1 38
ODBC settings not showng in FileMaker External Data Sources 6 23
SQL Server Express or Standard? 5 29
Need multiple Group By's 8 26
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

749 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