Arithmetic overflow error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Arithmetic overflow error

Author

Topic

joep
Starting Member

5 Posts

Posted - 2010-08-17 : 13:55:26

I keep getting this error message:

Arithmetic overflow error converting bigint to data type numeric.
Warning: Null value is eliminated by an aggregate or other SET operation.

I casted Bigint everywhere I could think of. Any help would be much appreciated. Thanks!

Code below: -
select Division,
cast(sum(C) as decimal(10,2))/cast(sum(SC) as decimal(10,2)) Result,
--ORBIT.dbo.fadhere(C,SC),
C Compliance,
SC Scheduled

from
(
SELECT Division,
sum(cast(ad.COMP AS bigint))as C,
sum(cast(ad.SCHED AS bigint))as SC

--cast(sum(ad.COMP)as decimal(18,0)) C,
--cast(sum(ad.SCHED)as decimal(18,0)) SC

FROM

--emp
(
SELECT UPPER(LTRIM(RTRIM(PP_NUMBER))) EmpID,
LTRIM(RTRIM(Lucent_Log_new)) LucID,
dbo.ProperCase(LAST_NAME)+', '+dbo.ProperCase(FIRST_NAME) Employee,
EMP_SK,
FIRST_NAME as FName,
LAST_NAME as LName,
EmpDivisionName as Division,
EmpGroupName as [Group],
EmpTeamName as Team,
EmpTeamID as TeamID,
EmpRoleName as [Role],
EmpRoleID as RoleID
FROM Employee.dbo.EmployeeArchive a,
Employee.dbo.emp_id_luc b
WHERE TERM_DATE_DATE9 is null and
EMP_ID = Lucent_Log_new and
[Is Lead?] = 'No' and
IS_MANAGER = 0 and
EmpDivisionID in ('0111022','0111001','0111053') and
EXISTS (
SELECT 1
FROM (
SELECT UPPER(LTRIM(RTRIM(PP_NUMBER))) EmpID,
MAX(ARCHIVE_DT) ARCHIVE_DT
FROM Employee.dbo.EmployeeArchive
WHERE ARCHIVE_DT between @startdate and dateadd(s,-1,@enddate+1)
GROUP BY UPPER(LTRIM(RTRIM(PP_NUMBER)))
) ad
WHERE UPPER(LTRIM(RTRIM(PP_NUMBER))) = EmpID and
ARCHIVE_DT = ARCHIVE_DT
))emp

LEFT OUTER JOIN

(
SELECT
EMP_SK,

sum(cast(SCHEDULED AS bigint))as SCHED,
sum(cast(COMPLIANCE AS bigint))as COMP
from
Employee.dbo.adhere
where NOM_DATE BETWEEN @startdate AND @enddate

GROUP BY EMP_SK) ad

ON emp.EMP_SK = ad.EMP_SK
group by Division) final
group by Division,C,SC

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-17 : 13:58:35

can you post your table schema.

joep
Starting Member

5 Posts

Posted - 2010-08-17 : 14:15:30

Comp and sched are both int

Field Data/type AllowNulls
SCHEDULED int Checked
COMPLIANCE int Checked

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-17 : 14:17:07

I would suggest that you break it up, and execute small pieces of code..from the inner out...to see which part is having the problem

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-17 : 14:28:19

Just a quick guess, but a BIGINT can be 19 places long, so I don't think a DECIMAL (10,2) will be enough..

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-17 : 14:35:22

I have an idea

Why don't you tell us what this is suppose to do, supply us with the ddl, some sample data (in DML form) and what the expected results for that sample are suppose to be

I think we need to do a rewrite here

it's a mess

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources