RE: SQL Server 2000 & SQL Server 2005

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Manon (manonr@xxxxxxxxxx) writes:
The Query I post on my database gave the same error:

SELECT TOP 10000 A.[unid]
FROM [dbo].[hardware] A INNER JOIN (SELECT TOP 1000 B.[recordid] AS
[recordid],B.[ranking] AS [ranking]
FROM [dbo].[hardware] C INNER JOIN (SELECT D.[recordid],(SUM(
D.[weight]))/(SUM( E.[count])) AS [ranking]
FROM (SELECT F.[recordid],F.[tableid],F.[woordid],((COUNT(
F.[woordid])+0.0))/(7) AS [weight],0 AS [ignore],CASE WHEN
F.[woordid]=N'5b0001' THEN 1 ELSE 0 END AS [notOr],0 AS [OrGroup0]
FROM [dbo].[woordindex] F WHERE (F.[tableid]=5) AND (F.[woordid]
IN(N'5b0001')) GROUP BY F.[recordid],F.[tableid],F.[woordid]) D INNER JOIN
[dbo].[recordwoordcount] E ON ((E.[recordid]=D.[recordid]) AND
(E.[tableid]=D.[tableid])) AND (ABS(E.[recordstatus])=1) GROUP BY
D.[recordid] HAVING SUM( D.[notOr])>=1) B ON C.[unid]=B.[recordid] WHERE
(C.[status]=1) OR (C.[status]=-1) ORDER BY ranking DESC) G ON
G.[recordid]=A.[unid] WHERE A.[status]=1 ORDER BY A.[naam] ASC

Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting numeric to data type numeric.

Any idea on how to determine which field no longer is correct?

Since you have a number of nested derived tabless, you can run the
inner queries, and see on which level the error start to appear.

My bet goes to one of the SUM functions.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages