SQL Server, Subqueries and data types

Today I had to deal with an issue on SQL Server 2005 that I thought might be worth sharing. Maybe someone else runs into a similar issue and can't figure out why SQL Server is playing stupid.

I was trying to run a query similar to the one below, it was generated by a reporting tool:

select sum(a11.COGS_ORDERED), 

sum(a11.LINE_RECEIVED)

from 
(SELECT 
ID
, COGS_ORDERED

, QTY_SHORTED
, 'LINE_RECEIVED' =
 CASE
   WHEN QTY_SHORTED = 0 THEN 1
   ELSE 0
 END
FROM myTable a11)

SQL Server always returned the error message "Arithmetic overflow error converting expression to data type int." . I figured out that the issue has to do with my case statement so I tested to run a sum right on the case statement and it worked fine:


SELECT 

SUM( CASE

   WHEN QTY_SHORTED = 0 THEN 1
   ELSE 0
 END)
FROM myTable a11

After doing some research I found out that SQL Server stores the result of the subquery as int and is not able to convert it to bigint when doing the SUM. Forcing a convert already in the case statement fixes the issue:

select sum(a11.COGS_ORDERED), 

sum(a11.LINE_RECEIVED)

from 
(SELECT 
ID
, COGS_ORDERED

, QTY_SHORTED
, 'LINE_RECEIVED' =
 CASE
   WHEN QTY_SHORTED = 0 THEN CONVERT(BIGINT,1)
   ELSE CONVERT(BIGINT,0)
 END
FROM myTable a11)

I think a database management system should be smart enough to do this conversion by itself.