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
Solved

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

Posted on 2008-10-28
5
424 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

860 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