SUM calculations are not returning correct results on SQL2005

Question

SUM calculations are not returning correct results on SQL2005

throneofmight

Mr or Mrs. 500

Points: 595
More actions
March 5, 2009 at 12:56 pm

#94729

I'm not a developer myself but one of my clients is having the following issue in one of our SQL2005 clustered servers (SQL Server 9.0.3042) and I really can't think on anything to help him/her out:
"SUM calculations are not returning correct results when used inside query with tables joined together. In some cases, it is causing a Arithmetic overflow error converting expression to data type int error"
I'm really not sure if this is related to the SQL server itself. There has been no recent patches applied into the machice. We plan to do CU10 + SNAC this coming weekend but that is about it.
Hope anyone can give me a hand on this. Thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

It may not be related to anything added to SQL Server but all Aggregate functions ignore nulls except COUNT(*) which counts all the rows so all aggregate operations with Nullable columns must add COUNT(*).

And OUTER JOINs default to mathematical null so you must also use ISNULL. So give the two links below to your customer lets hope the person understands the math.

http://www.sqlservercentral.com/articles/Advanced+Querying/gotchasqlaggregatefunctionsandnull/1947/

http://www.sqlservercentral.com/articles/Advanced+Querying/2829/

Kind regards,
Gift Peddie

throneofmight Mr or Mrs. 500 Points: 595 More actions · Answer 2

thanks for the info mate...the issue was code related indeed

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

I am glad I could help.

🙂

Kind regards,
Gift Peddie

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

mario (3/6/2009)
thanks for the info mate...the issue was code related indeed

Two way street, please... what was that code issue and how did you fix it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

throneofmight Mr or Mrs. 500 Points: 595 More actions · Answer 5

I did not actually fix it but here's what a friend helping me out suggested:

So, I suggest:

a) Recoding the query in such a way that the left outer join only returns the exact number of rows to consider in the result (not sure which is the best way to do that).

b) Use the query # 1 below, splitting the query up in 2 parts: the first one to get the 2 first variables (@EXCP_OC, @DEMAND) and the second part to get the other variable (@ACTUALS)

Hope this helps.

Thanks,

Luis

_______________________

Query # 1 – A possible solution

DECLARE @EXCP_OC Int, @DEMAND Int, @ACTUALS Int

DECLARE @DEMAND_NAME Char(21), @CURR_PERIOD Int

SET @DEMAND_NAME = 'S8PPYCVAR'

SET @CURR_PERIOD = 297480

Select @EXCP_OC = Max(pd.OVERCLOSE),

@DEMAND = Sum(IsNull(pd.DEMAND_REQ,0) + IsNull(pd.DEMAND_ADJUST,0))

From MES_PRODUCT_DEMAND pd

Where RTrim(pd.MKT_PRODUCT_NAME) = RTrim(@DEMAND_NAME)

And pd.PERIOD = @CURR_PERIOD

Select @ACTUALS = Sum(IsNull(tsl.QUANTITY,0))

From MES_PRODUCT_DEMAND pd

Join FA300_SHIPPED_LOTS tsl

On tsl.PERIOD = pd.PERIOD

And tsl.SITE_NAME = pd.SITE_NAME

And tsl.MKT_PRODUCT_NAME = pd.MKT_PRODUCT_NAME

Where RTrim(pd.MKT_PRODUCT_NAME) = RTrim(@DEMAND_NAME)

And pd.PERIOD = @CURR_PERIOD

SELECT @EXCP_OC, @DEMAND, @ACTUALS

Query # 2: Rows returned on each database (run separately on xxx & TESTxxx databases and see the number of rows returned in each case)

DECLARE @EXCP_OC Int, @DEMAND Int, @ACTUALS Int

DECLARE @DEMAND_NAME Char(21), @CURR_PERIOD Int

SET @DEMAND_NAME = 'S8PPYCVAR'

SET @CURR_PERIOD = 297480

Select *

From MES_PRODUCT_DEMAND pd

Left Outer Join FA300_SHIPPED_LOTS tsl

On tsl.PERIOD = pd.PERIOD

And tsl.SITE_NAME = pd.SITE_NAME

And tsl.MKT_PRODUCT_NAME = pd.MKT_PRODUCT_NAME

Where RTrim(pd.MKT_PRODUCT_NAME) = RTrim(@DEMAND_NAME)

And pd.PERIOD = @CURR_PERIOD

Query # 3: Original Query:

DECLARE @EXCP_OC Int, @DEMAND Int, @ACTUALS Int

DECLARE @DEMAND_NAME Char(21), @CURR_PERIOD Int

SET @DEMAND_NAME = 'S8PPYCVAR'

SET @CURR_PERIOD = 297480

Select @EXCP_OC = Max(pd.OVERCLOSE),

@DEMAND = Sum(IsNull(pd.DEMAND_REQ,0) + IsNull(pd.DEMAND_ADJUST,0)),

@ACTUALS = Sum(IsNull(tsl.QUANTITY,0))

From MES_PRODUCT_DEMAND pd

Left Outer Join FA300_SHIPPED_LOTS tsl

On tsl.PERIOD = pd.PERIOD

And tsl.SITE_NAME = pd.SITE_NAME

And tsl.MKT_PRODUCT_NAME = pd.MKT_PRODUCT_NAME

Where RTrim(pd.MKT_PRODUCT_NAME) = RTrim(@DEMAND_NAME)

And pd.PERIOD = @CURR_PERIOD

SELECT @EXCP_OC, @DEMAND, @ACTUALS