SQL Server Error: "Arithmetic overflow error converting numeric to data type numeric"

[Tim Howell]

I'm still quite new to SQL, and so I may be missing something quite
obvious. Apologies in advance if that's the case.

I've included my query at the bottom of the message as it is quite long;
my problem is that I get "Arithmetic overflow error converting numeric
to data type numeric" when the first parameter to my COALESCE calls is
not NULL. That is, the COALESCEs and the query work as I expect when
SUM(cust_vw_actual.Actual) is NULL (they return 0.00), but if it isn't I
get the error. I am also getting "Warning: Null value eliminated from
aggregate."

SELECT cust_vw_budget.BeginDate, cust_vw_budget.DeptNu,
cust_vw_budget.AcctNu AS ReportAcctNu, cust_vw_budget.Period,
SUM(cust_vw_budget.Budget) AS BudgetPeriod,cust_vw_actual.BeginDate,
cust_vw_actual.DeptNu, cust_vw_actual.AcctNu, cust_vw_actual.Period,
COALESCE(SUM(cust_vw_actual.Actual), 0.00) AS ActualPeriod,
COALESCE(SUM(cust_vw_actual.Actual), 0.00) - SUM(Budget) AS
VariancePeriod

FROM cust_vw_budget, cust_vw_actual

WHERE cust_vw_budget.BeginDate='5/1/2002' AND
cust_vw_budget.BeginDate=cust_vw_actual.BeginDate AND
cust_vw_budget.DeptNu=10 AND cust_vw_budget.DeptNu=cust_vw_actual.DeptNu
AND cust_vw_budget.Period=10 AND
cust_vw_budget.Period=cust_vw_actual.Period AND
cust_vw_budget.AcctNu*=cust_vw_actual.AcctNu

GROUP BY cust_vw_budget.BeginDate, cust_vw_budget.DeptNu,
cust_vw_budget.AcctNu, cust_vw_budget.Period, cust_vw_actual.BeginDate,
cust_vw_actual.DeptNu, cust_vw_actual.AcctNu, cust_vw_actual.Period;

[Jenda Krynicky]

From: "Tim Howell" <t...@fefcful.org>

> I'm still quite new to SQL, and so I may be missing something quite
> obvious. Apologies in advance if that's the case.
>
> I've included my query at the bottom of the message as it is quite
> long; my problem is that I get "Arithmetic overflow error converting
> numeric to data type numeric" when the first parameter to my COALESCE
> calls is not NULL. That is, the COALESCEs and the query work as I
> expect when SUM(cust_vw_actual.Actual) is NULL (they return 0.00), but
> if it isn't I get the error. I am also getting "Warning: Null value
> eliminated from aggregate."

What's the type of the cust_vw_actual.Actual column?

To get rid of the warning you could replace the
SUM(cust_vw_actual.Actual)
by
SUM(COALESCE(cust_vw_actual.Actual,0.0))

and (unless the Budget column doesn't allow NULLs)
SUM(Budget)
by
SUM(COALESCE(Budget, 0.0))

Jenda
===== Je...@Krynicky.cz === http://Jenda.Krynicky.cz =====
When it comes to wine, women and song, wizards are allowed
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery

[Tim Howell]

The cust_vw_actual.Actual column is type Money.

--TWH

-----Original Message-----
From: Jenda Krynicky [mailto:Je...@Krynicky.cz]
Sent: Friday, April 25, 2003 10:09 AM
To: DBI Users
Subject: Re: SQL Server Error: "Arithmetic overflow error converting
numeric to data type numeric"

<my original message snipped>

[Jenda Krynicky]

From: "Tim Howell" <t...@fefcful.org>

> The cust_vw_actual.Actual column is type Money.

And Budget? Maybe these two are of different types and the
subtraction fails.
Try to comment out the

COALESCE(SUM(cust_vw_actual.Actual), 0.00) - SUM(Budget) AS
VariancePeriod

Does the error go away? If so put it back and try to convert one to
the type of the other.

I thought maybe the
coalesce(sum(cust_vw_actual.Actual),0.0)
returns the error, but it doesn't seem to be the case. I tried to
create a table with a Money column and tried similar query and it
worked fine.

[Tim Howell]

I found my problem: it was the period I was using in 0.00

I changed it to simply 0 and it works fine.

--TWH

-----Original Message-----
From: Jenda Krynicky [mailto:Je...@Krynicky.cz]
Sent: Friday, April 25, 2003 12:10 PM
To: dbi-...@perl.org
Subject: RE: SQL Server Error: "Arithmetic overflow error converting
numeric to data type numeric"