solve numeric overflow problem when converting to float

views:

answers:

solve numeric overflow problem when converting to float

I have a float value that i need to do some calculations on and insert into a numeric(9,2). However in rare (and likley erronous) cases there are some data anomolies and I end up with a value that will not fit into numeric(9,2).

What is a good solution to this problem? Maybe just use 9999999.99 if the number is 9999999.99 or greater?

Any better ideas (besides of course changing the column data type)

Thanks!

EDIT:

I just wanna make the thing run without error. I can't "correct" the data. I can't omit it because then when I do a sumation there will be no indication that this value is wrong. We are talking a FRACTION of a FRACTION of a percent of the data has this issue. Just need to make the query run.

+1 A:

Truncation / Rounding is not an option?

How about NULL instead?

Having a special data value for that condition doesn't smell right.

Rawheiser 2010-08-23 17:45:57

Having erroneous data dosent smell right, unfortunatly that what I have to work with. At some point i'm going to do some group by/sumations on this col, so as long as the sumations are a lot larger than they should be i'm happy. I should mention we are talking like less than a dozen data values out of 1.5 millions rows on a calculation proccess that likley is less than 70% accurate.

kralco626 2010-08-23 17:56:54

Statistically, it makes more sense to remove outliers from the population, setting them to null would do that in a sum() or count() function.

Rawheiser 2010-08-24 14:04:53

+1 Ya I thought about that. I consulted the buisness and they would prefer to see the data "stick out" rather than "blend in".

kralco626 2010-08-25 12:53:59

Add an independent overflow field or variable, or introduce an error condition.

Beth 2010-08-23 17:48:56

The problem still stand. What do i do with the value? Even If i set a flag.

kralco626 2010-08-23 18:05:39

If you set a flag, you mark a condition in which the value can be ignored. At that point, you can set it to whatever you want. 0, null, max, -1, take your pick, it won't be used.

Beth 2010-08-23 22:58:11

We've had some bad dates in upstream systems we couldn't change, but could test for. We end up ignoring them because, as you say, you want the thing to run and the error from ignoring is so small compared to the error in the rest of the data, it's ok to disregard it.

Beth 2010-08-23 23:03:41

Thanks Beth. Thats actually a very usable solution, rather than just ratting on my data like everyone else. If I could fix it I would, but I would like to know someone who has better than 99.9992% data acuracy in a system with around 20,000,000 records a year. I'm not sure exactly what i'm going to end up doing, but you def had the best and most helpful answer here.

kralco626 2010-08-24 10:36:29

why the downvote?

Beth 2010-08-24 21:01:14

@Beth - likley just because I selected your answer and certin other people hate my question so they are taking it out on you. In my opinion, if someone leaves a downvote they should be forced to leave a comment as to why. No reason your answer should be down voted, sounds like a reasonable solution to me.

kralco626 2010-08-25 12:56:15

Your question could have been worded better, but that can be said of most questions posted. Many responders make assumptions without stating them in their answers. The most you can do in your position is create a report noting the errors.

Beth 2010-08-25 13:11:55

@Beth - agreed. I would vote to close this questions, but I don't have enough rep.

kralco626 2010-08-25 17:24:20

+4 A:

If you believe the case is erroneous then an error should be raised and the original data used for the calculation should be corrected. Do not mask the original problem and perpetuate the error deeper into your system by trying to store some artificial value.

Joe Stefanelli 2010-08-23 17:51:03

i WISH I could do that! great solution in theory, but not possible. Got to work with what I have.

kralco626 2010-08-23 17:54:14

If you care so little about your data, just use `select cast(rand()*10000000 as numeric(9,2))`

Joe Stefanelli 2010-08-23 18:23:26

+1 Well said...

gbn 2010-08-23 19:22:01

very funny joe. I really would like you to tell me how i'm supposed to fix my data. It's customer account data. If people mess up a few numbers, or if some electronic input device fails or preforms abnormally what am i supposed to do? Going back to the buisness and asking them to correct them? they have no way to do that

kralco626 2010-08-24 10:45:21

I'll apologize for giving in to my sarcastic sense of humor in my previous comment, but I stand by my original answer. Bad data is bad. Period. Get it out of the main flow of your system as soon as you identify it. Even if the correction is as simple as "flag and skip this record", you need to respond proactively to the situation, rather than fudging the data as you had proposed.

Joe Stefanelli 2010-08-24 13:43:21

+1 @joe - no need to apologize lol. I just can't ignore the fact that the data is there though... I really wish I could delete this quetion, this is way to difficult of a proccess to try to explain and get help on. In general Joe, I agree with you, I would always try to validate the input data, but my hands are tied...

kralco626 2010-08-25 12:59:53

+3 A:

this is a business decision! how can I possibly know your application and guess what you should do when there is a problem? If this is an accounting application, round the number up and deposit the money in my account!

You should validate the data before the calculation and stop with a message/error log noting the problem value. Any "fake" value you enter into this column will need to be handled by other code, so don't do that.

KM 2010-08-23 17:52:33

haha. the business decision is we will take the additional .0008 % inaccuracy (a dozen of these overflow values out of 1.5 million) on a ESTIMATION proccess that already has a large % error. Just make the thing run.

kralco626 2010-08-23 17:59:26

@kralco626 said `haha. the business decision is we will take the additional .0008 % inaccuracy (a dozen of these overflow values out of 1.5 million) on a ESTIMATION proccess that already has a large % error. Just make the thing run.` **I say haa haa to you** your vague question failed to mention that? am I supposed to know your app or read your mind? in the question, you failed to mention any details about what you are doing or what impact the bad data has on the calculation, or even what the final calculation is used for. so, it is **a business decision** not a decision I can make for you

KM 2010-08-25 12:32:32

@KM - chill buddy, i was just laughing at your joke about rounding the money up and putting it in your account. And your right, it was a badly worded question. I was just wondering if anyone else had ever run into this issue and how they may have solved it. I was looking for some suggestions. Anyways, I tried to delete the question, but it's been here too long it wont let me.

kralco626 2010-08-25 12:52:38

ansaurus

tags:

views:

answers:

solve numeric overflow problem when converting to float

related questions