September 1, 2009 at 4:33 pm
I have a simple select statement that looked like this
select top 1000 convert(varchar(30), convert(money, isnull(sum(shippingcost),0))) as "Total Navigation" from dbo.NavAttempts
This worked in the past but all of a sudden I start getting the error "Arithmetic overflow error converting expression to data type money".
The data type of the cost column is "money". I have tried all sorts of casting and converting mixture and still get the error. What other possible reasons could there be for this error? Thanks
September 1, 2009 at 5:01 pm
Strange. any chance the SUM is larger than a MONEY datatype can hold?
Why convert money to money?
select top 1000 convert(varchar(30), coalesce(sum(shippingcost),$0.0)) as "Total Navigation"
from dbo.NavAttemptsAlso, why convert money to varchar?
September 1, 2009 at 5:19 pm
still getting the same error, I wonder what is going on, the money to money and varchar conversion are for formatting and reporting purposes ( i know i know let the front end handle that :-D). Thanks for your help though!
September 1, 2009 at 9:02 pm
Check the data, run this:
select top 1000
shippingcost
from
dbo.NavAttempts
September 2, 2009 at 9:34 am
Hey Lynn, thanks for the help. I checked the data already, it has some nulls in it. Coalesce and isnull should fix this, tried both but still getting the same error.
Arithmetic overflow error converting expression to data type money.
Warning: Null value is eliminated by an aggregate or other SET operation.
This is extremely strange!!
September 2, 2009 at 9:39 am
Remove the convert to money from the code, to see the real total for those 1000 rows, it might be more than what a money field can hold?
Cheers,
J-F
September 2, 2009 at 9:47 am
I know what the problem is. Here is your code:
select top 1000
convert(varchar(30), convert(money, isnull(sum(shippingcost),0))) as "Total Navigation"
from
dbo.NavAttempts
The problem is that the TOP 1000 does not restrict the SUM to the first 1000 records from dbo.NavAttempts. It is summing shippingcost from all the rows in dbo.NavAttempts.
September 2, 2009 at 9:47 am
I don't think it helps your particular issue, but you could change the order of COALESE and SUM to get rid of that warning message:select top 1000 convert(varchar(30), sum(coalesce(shippingcost,$0.0))) as "Total Navigation"
from dbo.NavAttempts
Is the table you are quering actually a view?
September 2, 2009 at 9:49 am
Thanks for the suggestion. I even did the real total for the top 2 only..surely the figures can't be bigger than money datatype. What makes it even stranger is that the same syntax worked before and stopped working all of a sudden.
September 2, 2009 at 9:54 am
This is truly a very strange scenario, I have tried all the codes and I still get the same error, the one with the coalesce did eliminate the WARNING error though but I have no clue why this is happening. I appreciate all the help guys. I think it's time to probe and ask who changed what !!
September 2, 2009 at 10:13 am
Try this:
with MyNavAttempts as (
select top 1000
isnull(shippingcost,0.00) as shippingcost
from
dbo.NavAttempts
)
select
sum(shippingcost)
from
MyNavAttempts;
September 2, 2009 at 10:30 am
Hey Lynn, the CTE was actually a nice way to get it to work. I also noticed something strange with this as well. If I select top 1000, i get a value no error
with MyNavAttempts as (
select top 1000
isnull(shippingcost,0.00) as shippingcost
from
dbo.NavAttempts
)
select
sum(shippingcost)
from
MyNavAttempts;
If I just select everything, I get the Arithmetic overflow error again.
with MyNavAttempts as (
select isnull(shippingcost,0.00) as shippingcost
from
dbo.NavAttempts
)
select
sum(shippingcost)
from
MyNavAttempts;
Now for the strange part, if I select top 10000000000000 (i don't have that many rows), I get no errors. It is very mind boggling. But thanks for the great idea.
with MyNavAttempts as (
select top 1000000000000000000
isnull(shippingcost,0.00) as shippingcost
from
dbo.NavAttempts
)
select
sum(shippingcost)
from
MyNavAttempts;
September 2, 2009 at 11:07 am
Lynn Pettis (9/2/2009)
I know what the problem is. Here is your code:
select top 1000
convert(varchar(30), convert(money, isnull(sum(shippingcost),0))) as "Total Navigation"
from
dbo.NavAttempts
The problem is that the TOP 1000 does not restrict the SUM to the first 1000 records from dbo.NavAttempts. It is summing shippingcost from all the rows in dbo.NavAttempts.
Ahh, yeah. I bet that is the issue..
Try changing it to something like:SELECT
convert(varchar(30), sum(shippingcost)) as "Total Navigation"
FROM
(
select top 1000
COALESCE(shippingcost, $0.00) as shippingcost
from
dbo.NavAttempts
) AS TEDIT: Doh.. I didn't see there was a second page...:-D
September 3, 2009 at 10:00 am
Hey guys, I have a question about money data types, if his "shippingcost" is to large to be held by the "money" data type what is a better (bigger) option for a data type to hold money?
September 3, 2009 at 10:54 am
iruagawal (9/3/2009)
Hey guys, I have a question about money data types, if his "shippingcost" is to large to be held by the "money" data type what is a better (bigger) option for a data type to hold money?
Decimal (or Numeric) would work. Some people prefer to use Decimal because of a potential math issue with Money data if you are doing a lot of math on the data.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply