Skip to main content

Posts

Showing posts from July, 2011

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 th…