Arithmetic Overflow when comparing working select items

• lonnie.hull

  SSC Veteran

  Points: 204

  More actions

  November 28, 2018 at 8:09 am

  #416520

  Hello,

  I have a working query that gives me the data I'm looking for.

  SELECT DISTINCT
CAST(COALESCE(LEFT(o.Part_Family_Code,2),0) AS decimal (10,2)) AS "FBGM",
ROUND(ISNULL(q.Profit_Percent + cd.Commission_Percent,
q.Profit_Percent) ,2) AS "Budgeted",
oh.Order_Number AS "Order",
o.Work_Code AS "Work_Code",
s.Address__Email_Address AS "Address__Email_Address",
vq.Salesman_Name AS "Salesman_Name",
cd.Commission_Percent AS "Commission_Percent",
o.Employee_Code_Routed_By AS "Employee_Code_Routed_By",
o.Product_Code AS "Product_Code",
o.Part_Number AS "Part_Number",
CAST(COALESCE(LEFT(o.Part_Family_Code,2),0) *.9 AS decimal (10,2)) AS "FBGMx9",
CAST(COALESCE(LEFT(o.Part_Family_Code,2),0) *1.1 AS decimal (10,2)) AS "FBGMx11"
FROM dbo.Order_Detail o
LEFT OUTER JOIN dbo.Order_Header oh ON o.Order_Header_ID = oh.Order_Header_ID
LEFT OUTER JOIN dbo.Quantity_Break_Detail q ON o.Order_Detail_ID = q.Order_Detail_ID
LEFT OUTER JOIN dbo.Billing_Detail b ON oh.Part_Number_ID = b.Part_Number_ID
LEFT OUTER JOIN dbo.vQV_SalesmanCode_Orders vq ON o.Order_Detail_ID = vq.Order_Detail_ID
LEFT OUTER JOIN dbo.Commission_Distribution cd ON o.Order_Detail_ID = cd.Order_Detail_ID
LEFT OUTER JOIN dbo.vSalesman_Code s ON cd.Salesman_Code = s.Salesman_Code
WHERE q.Profit_Percent <> 0 AND
o.Profit_Percent <> 0 AND
o.Status IN ( 'Firm' , 'In Process' , 'Released' ) AND
o.Part_Family_Code NOT IN ( '' , '0% - FBGM - WO' , '99' , '99% - FBGM' )
ORDER BY oh.Order_Number ASC

  When I add in this filter I get "Arithmetic Overflow Error converting varchar to data type numeric"

  AND ROUND(ISNULL(q.Profit_Percent + cd.Commission_Percent, q.Profit_Percent) ,2) NOT BETWEEN 'CAST(COALESCE(LEFT(o.Part_Family_Code,2),0) *.9 AS decimal (10,2)) ' AND 'CAST(COALESCE(LEFT(o.Part_Family_Code,2),0) *1.1 AS decimal (10,2)) ' 

  As all the criteria work in the select statement, I'm not certain where to start. Any help would be appreciated.

• Thom A

  SSC Guru

  Points: 99126

  More actions

  November 28, 2018 at 8:22 am

  #2014805

  The error is telling you the problem here. One of the following expressions is returning a value greater than 99999999.99 or less than -99999999.99:
  LEFT(o.Part_Family_Code,2),0) *.9
LEFT(o.Part_Family_Code,2),0) *1.1
  We can't see your data, so we can't tell you which row; you'll need to inspect your data to find out. Once you find out the largest/smallest numbers you'll dealing with, you'll to increase the precision of your decimal to handle it.

  (P.s. I assume those CAST expressions aren't meant to be wrapped in single quotes?)

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  November 28, 2018 at 8:24 am

  #2014806

  I would guess that Profit_Percent and Commission_Percent have character data types, and there's values in one or both of them that can't be converted to decimal(10,2).

  John

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  November 28, 2018 at 8:24 am

  #2014807

  lonnie.hull - Wednesday, November 28, 2018 8:09 AM

  Hello,

  I have a working query that gives me the data I'm looking for.

  SELECT DISTINCT
CAST(COALESCE(LEFT(o.Part_Family_Code,2),0) AS decimal (10,2)) AS "FBGM",
ROUND(ISNULL(q.Profit_Percent + cd.Commission_Percent,
q.Profit_Percent) ,2) AS "Budgeted",
oh.Order_Number AS "Order",
o.Work_Code AS "Work_Code",
s.Address__Email_Address AS "Address__Email_Address",
vq.Salesman_Name AS "Salesman_Name",
cd.Commission_Percent AS "Commission_Percent",
o.Employee_Code_Routed_By AS "Employee_Code_Routed_By",
o.Product_Code AS "Product_Code",
o.Part_Number AS "Part_Number",
CAST(COALESCE(LEFT(o.Part_Family_Code,2),0) *.9 AS decimal (10,2)) AS "FBGMx9",
CAST(COALESCE(LEFT(o.Part_Family_Code,2),0) *1.1 AS decimal (10,2)) AS "FBGMx11"
FROM dbo.Order_Detail o
LEFT OUTER JOIN dbo.Order_Header oh ON o.Order_Header_ID = oh.Order_Header_ID
LEFT OUTER JOIN dbo.Quantity_Break_Detail q ON o.Order_Detail_ID = q.Order_Detail_ID
LEFT OUTER JOIN dbo.Billing_Detail b ON oh.Part_Number_ID = b.Part_Number_ID
LEFT OUTER JOIN dbo.vQV_SalesmanCode_Orders vq ON o.Order_Detail_ID = vq.Order_Detail_ID
LEFT OUTER JOIN dbo.Commission_Distribution cd ON o.Order_Detail_ID = cd.Order_Detail_ID
LEFT OUTER JOIN dbo.vSalesman_Code s ON cd.Salesman_Code = s.Salesman_Code
WHERE q.Profit_Percent <> 0 AND
o.Profit_Percent <> 0 AND
o.Status IN ( 'Firm' , 'In Process' , 'Released' ) AND
o.Part_Family_Code NOT IN ( '' , '0% - FBGM - WO' , '99' , '99% - FBGM' )
ORDER BY oh.Order_Number ASC

  When I add in this filter I get "Arithmetic Overflow Error converting varchar to data type numeric"

  AND ROUND(ISNULL(q.Profit_Percent + cd.Commission_Percent, q.Profit_Percent) ,2) NOT BETWEEN 'CAST(COALESCE(LEFT(o.Part_Family_Code,2),0) *.9 AS decimal (10,2)) ' AND 'CAST(COALESCE(LEFT(o.Part_Family_Code,2),0) *1.1 AS decimal (10,2)) ' 

  As all the criteria work in the select statement, I'm not certain where to start. Any help would be appreciated.

  Kindly check the data type of Part_Family_Code,Profit_Percent and Commission_Percent columns. Your basically try to non integer/number values (alphabets or special character) into integer value. I suspect that Part_Family_Code column may contain non numeric values.

  Saravanan

• Thom A

  SSC Guru

  Points: 99126

  More actions

  November 28, 2018 at 8:30 am

  #2014812

  saravanatn - Wednesday, November 28, 2018 8:24 AM

  Kindly check the data type of Part_Family_Code,Profit_Percent and Commission_Percent columns. Your basically try to non integer/number values (alphabets or special character) into integer value. I suspect that Part_Family_Code column may contain non numeric values.

  That would result in an error like: Error converting data type varchar to numeric. The error the OP has (Arithmetic overflow) means the value is too large, in precision terms, to fit into the data type. For example:
  SELECT CONVERT(decimal(10,2), 'a1729172');
/*
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
*/
GO
SELECT CONVERT(decimal(10,2),'1234567890.12');
/*
Msg 8115, Level 16, State 8, Line 7
Arithmetic overflow error converting varchar to data type numeric.
/*


  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  November 28, 2018 at 8:36 am

  #2014815

  Thom A - Wednesday, November 28, 2018 8:29 AM

  saravanatn - Wednesday, November 28, 2018 8:24 AM

  Kindly check the data type of Part_Family_Code,Profit_Percent and Commission_Percent columns. Your basically try to non integer/number values (alphabets or special character) into integer value. I suspect that Part_Family_Code column may contain non numeric values.

  That would result in an error like: Error converting data type varchar to numeric. The error the OP has (Arithmetic overflow) means the value is too large, in precision terms, to fit into the data type. For example:
  SELECT CONVERT(decimal(10,2), 'a1729172');
/*
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
*/
GO
SELECT CONVERT(decimal(10,2),'1234567890.12');
/*
Msg 8115, Level 16, State 8, Line 7
Arithmetic overflow error converting varchar to data type numeric.
/*


  Sorry . Nice catch Thorn

  Saravanan

• lonnie.hull

  SSC Veteran

  Points: 204

  More actions

  November 28, 2018 at 8:45 am

  #2014819

  Thom A - Wednesday, November 28, 2018 8:22 AM

  The error is telling you the problem here. One of the following expressions is returning a value greater than 99999999.99 or less than -99999999.99:
  LEFT(o.Part_Family_Code,2),0) *.9
LEFT(o.Part_Family_Code,2),0) *1.1
  We can't see your data, so we can't tell you which row; you'll need to inspect your data to find out. Once you find out the largest/smallest numbers you'll dealing with, you'll to increase the precision of your decimal to handle it.

  (P.s. I assume those CAST expressions aren't meant to be wrapped in single quotes?)

  Thom,

  I guess my issue is that these two lines
  CAST(COALESCE(LEFT(o.Part_Family_Code,2),0) *.9 AS decimal (10,2)) AS "FBGMx9",
CAST(COALESCE(LEFT(o.Part_Family_Code,2),0) *1.1 AS decimal (10,2)) AS "FBGMx11"


  return valid data when I run the query.
  Why would it throw an error when I'm using the exact same criteria to compare?

  Thanks for your help

• Thom A

  SSC Guru

  Points: 99126

  More actions

  November 28, 2018 at 8:56 am

  #2014821

  lonnie.hull - Wednesday, November 28, 2018 8:45 AM

  Thom,

  I guess my issue is that these two lines
  CAST(COALESCE(LEFT(o.Part_Family_Code,2),0) *.9 AS decimal (10,2)) AS "FBGMx9",
CAST(COALESCE(LEFT(o.Part_Family_Code,2),0) *1.1 AS decimal (10,2)) AS "FBGMx11"


  return valid data when I run the query.
  Why would it throw an error when I'm using the exact same criteria to compare?

  Thanks for your help

  OK, then it's got to be:
  ISNULL(q.Profit_Percent + cd.Commission_Percent, q.Profit_Percent)
  ISNULL uses the datatype of the first parameter, so, at a guess, the value of q.Profit_Percent has a higher precision than q.Profit_Percent + cd.Commission_Percent for a given row. So, for example, the following generates an error:
  CREATE TABLE #temp (d1 decimal(5,2), d2 decimal(10,2));

  INSERT INTO #temp (d1,d2)
VALUES (123.45,12345678.90),(NULL,123456.88);
GO
SELECT ISNULL(d1,d2)
FROM #temp;
GO
DROP TABLE #temp;

  Check your datatype of q.Profit_Percent + cd.Commission_Percent and q.Profit_Percent. q.Profit_Percent cannot have a higher precision than the other expression.
  

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• lonnie.hull

  SSC Veteran

  Points: 204

  More actions

  November 28, 2018 at 10:25 am

  #2014842

  Thom,

  Just for testing I eliminated the ISNULL

  ROUND(q.Profit_Percent + cd.Commission_Percent, 2) AS "Budgeted",

  This still returns the records I need (As far as I can tell).

  When I add the updated filter
  ROUND(q.Profit_Percent + cd.Commission_Percent, 2) NOT BETWEEN 'CAST(COALESCE(LEFT(o.Part_Family_Code,2),0) *.9 AS decimal (10,2)) ' AND 'CAST(COALESCE(LEFT(o.Part_Family_Code,2),0) *1.1 AS decimal (10,2)) '

  I get the Arithmetic overflow error varchar to data type numeric

  I know this is somewhat a work around, but could I create a local variable(s) (I've never done it in SQL) that contains the value given to me by the 3 items I'm using to compare? Does that sound like something that would work?

  As always, thanks for your time and effort.

• Thom A

  SSC Guru

  Points: 99126

  More actions

  November 28, 2018 at 11:03 am

  #2014852

  Why are your other 2 expressions always in single quotes? That's not a valid expression as is. This isn't dynamic SQL is it and you're just not giving us the full picture, is it?

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Thom A

  SSC Guru

  Points: 99126

  More actions

  November 28, 2018 at 11:06 am

  #2014854

  I'm afraid, however, at this stage I'm out of ideas. Without sample data to test against I have no way to replicate the issue, and I'm out "educated" guesses at this stage (unless it really is dynamic SQL).

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• lonnie.hull

  SSC Veteran

  Points: 204

  More actions

  November 28, 2018 at 11:23 am

  #2014859

  Thom,

  Thanks for the help.

  I took a different route and it appears to be working.
  SELECT DISTINCT
CAST(COALESCE(LEFT(o.Part_Family_Code,2),0) AS decimal (10,2)) AS FBGM,
ROUND(ISNULL(q.Profit_Percent + cd.Commission_Percent, q.Profit_Percent) ,2) AS Budgeted,
oh.Order_Number AS Order_Number,
o.Work_Code AS Work_Code,
s.Address__Email_Address AS Email_Address,
vq.Salesman_Name AS Salesman_Name,
cd.Commission_Percent AS Commission_Percent,
o.Employee_Code_Routed_By AS Routed_By,
o.Product_Code AS Product_Code,
o.Part_Number AS Part_Number,
CAST(COALESCE(LEFT(o.Part_Family_Code,2),0) *.9 AS decimal (10,2)) AS FBGMx9,
CAST(COALESCE(LEFT(o.Part_Family_Code,2),0) *1.1 AS decimal (10,2)) AS FBGMx11

  FROM dbo.Order_Detail o
LEFT OUTER JOIN dbo.Order_Header oh ON o.Order_Header_ID = oh.Order_Header_ID
LEFT OUTER JOIN dbo.Quantity_Break_Detail q ON o.Order_Detail_ID = q.Order_Detail_ID
LEFT OUTER JOIN dbo.Billing_Detail b ON oh.Part_Number_ID = b.Part_Number_ID
LEFT OUTER JOIN dbo.vQV_SalesmanCode_Orders vq ON o.Order_Detail_ID = vq.Order_Detail_ID
LEFT OUTER JOIN dbo.Commission_Distribution cd ON o.Order_Detail_ID = cd.Order_Detail_ID
LEFT OUTER JOIN dbo.vSalesman_Code s ON cd.Salesman_Code = s.Salesman_Code

  WHERE
q.Profit_Percent <> 0 AND
o.Profit_Percent <> 0 AND
o.Status IN ( 'Firm' , 'Released' ) AND
o.Part_Family_Code NOT IN ( '' , '0% - FBGM - WO' , '99' , '99% - FBGM' ) AND
ROUND(q.Profit_Percent + cd.Commission_Percent, 2) NOT BETWEEN CONVERT(INT, LEFT(o.Part_Family_Code,2)) *.9 AND
CONVERT(INT, LEFT(o.Part_Family_Code,2)) *1.1

  ORDER BY oh.Order_Number ASC

  By taking out the ISNULL and Coalesce I do need to verify that I'm getting all my records, but I've eliminated the error.

  Thanks again for your time.