SUM calculations are not returning correct results on SQL2005
-
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
-
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 -
thanks for the info mate...the issue was code related indeed
-
I am glad I could help.
Kind regards,
Gift Peddie -
mario (3/6/2009)
thanks for the info mate...the issue was code related indeedTwo 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) -
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply