Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

911 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

20 Experts available now in Live!

Get 1:1 Help Now