Bens Blog

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 do