Error : Arithmetic overflow error converting numeric to data type varchar.

• ashuthinks

  SSCommitted

  Points: 1698

  More actions

  December 21, 2011 at 8:43 am

  #250246

  getting error in following script but now able to find out where please help.

  DECLARE @CurrentDate DATETIME --= Fireball_Configuration.dbo.PreviousBusinessDay()

  DECLARE @CompareDate DATETIME = Fireball_Configuration.dbo.PreviousBusinessDayByDate(@CurrentDate)

  DECLARE @count INT = 5

  DECLARE @EmailHeader VARCHAR(500) = Fireball_Configuration.dbo.GetConfigurationValue('Fireball', 'EmailSettings - Header')

  DECLARE @EmailFooter VARCHAR(500)= Fireball_Configuration.dbo.GetConfigurationValue('Fireball', 'EmailSettings - Footer')

  DECLARE @tableHTML VARCHAR(MAX)

  CONVERT(VARCHAR(8),CONVERT(DECIMAL(8,4),((CurrentLoans.Price - PreviousLoans.Price) / PreviousLoans.Price) * 100)) + '%' AS Delta

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  December 21, 2011 at 8:50 am

  #1424297

  My random guess: -

  DECLARE @CurrentDate DATETIME --= Fireball_Configuration.dbo.PreviousBusinessDay()

  SELECT @CurrentDate = MAX(DATE)

  FROM Fireball_Reporting..Reporting_LoanPrices

  DECLARE @CompareDate DATETIME = Fireball_Configuration.dbo.PreviousBusinessDayByDate (@CurrentDate)

  DECLARE @count INT = 5

  DECLARE @EmailHeader VARCHAR(500) = Fireball_Configuration.dbo.GetConfigurationValue ('Fireball', 'EmailSettings - Header')

  DECLARE @EmailFooter VARCHAR(500) = Fireball_Configuration.dbo.GetConfigurationValue ('Fireball', 'EmailSettings - Footer')

  DECLARE @tableHTML VARCHAR(MAX)

  SET @tableHTML = @tableHTML + '<H1>Top ' + CONVERT(VARCHAR(20), @COUNT) + ' Distressed Losers</H1>' +

  '<TABLE border = "1" cellspacing="0" cellpadding="5">' + '<TR><TH>Company</TH><TH>

  Security</TH><TH>Current Date</TH><TH>Current Price</TH><TH>Compare Date</TH><TH>

  Compare Price</TH><TH>Delta</TH></TR>' + CAST((

  SELECT TD = CONVERT(VARCHAR(20), CompanyName), '', TD = CONVERT(VARCHAR(20), SecurityName),

  '', TD = CONVERT(VARCHAR(20), CurentDate), '', TD = CONVERT(VARCHAR(20), CurrentPrice), '',

  TD = CONVERT(VARCHAR(20), PreviousDate), '', TD = CONVERT(VARCHAR(20), PreviousPrice), '',

  TD = CONVERT(VARCHAR(20), Delta)

  FROM (

  SELECT TOP (@count) CONVERT(VARCHAR(10), CurrentLoans.DATE, 101) AS CurentDate,

  CONVERT(VARCHAR(10), PreviousLoans.DATE, 101) AS PreviousDate, CurrentLoans.CompanyName,

  CurrentLoans.SecurityName, CONVERT(VARCHAR(20), CurrentLoans.SecurityId) AS CurrentSecID,

  CONVERT(VARCHAR(20), PreviousLoans.SecurityId) AS PreviousSecID,

  CONVERT(VARCHAR(20), CurrentLoans.Price) AS CurrentPrice,

  CONVERT(VARCHAR(20), PreviousLoans.Price) AS PreviousPrice,

  CONVERT(VARCHAR(8), CONVERT(DECIMAL(8, 4), ((CurrentLoans.Price - PreviousLoans.Price) / PreviousLoans.Price) * 100)) + '%' AS Delta

  FROM (

  SELECT LoanPrices.DATE, LoanPrices.SecurityId, Company.NAME CompanyName, Security.NAME SecurityName, (LoanPrices.Bid + LoanPrices.Ask) / 2 Price

  FROM Fireball_Reporting..Reporting_LoanPrices LoanPrices

  INNER JOIN Fireball..Security ON Security.SecurityId = LoanPrices.SecurityId

  INNER JOIN Fireball..Company ON Company.CompanyId = Security.CompanyId

  ) CurrentLoans

  INNER JOIN (

  SELECT LoanPrices.DATE, LoanPrices.SecurityId, Company.NAME CompanyName, Security.NAME SecurityName, (LoanPrices.Bid + LoanPrices.Ask) / 2 Price

  FROM Fireball_Reporting..Reporting_LoanPrices LoanPrices

  INNER JOIN Fireball..Security ON Security.SecurityId = LoanPrices.SecurityId

  INNER JOIN Fireball..Company ON Company.CompanyId = Security.CompanyId

  ) PreviousLoans ON PreviousLoans.SecurityId = CurrentLoans.SecurityId AND PreviousLoans.DATE = @CompareDate

  WHERE CurrentLoans.DATE = @CurrentDate AND CurrentLoans.Price <> PreviousLoans.Price AND CurrentLoans.Price < 70

  ORDER BY (CurrentLoans.Price - PreviousLoans.Price) / PreviousLoans.Price ASC

  ) Movers

  FOR XML PATH('TR'), TYPE

  ) AS VARCHAR(MAX)) + '</TABLE>' + @EmailFooter;

  Without DDL, it's pretty much impossible to be sure though.

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• ashuthinks

  SSCommitted

  Points: 1698

  More actions

  December 21, 2011 at 8:51 am

  #1424299

  where exactly change is needed ?

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  December 21, 2011 at 8:52 am

  #1424300

  ashuthinks (12/21/2011)

  ---

  where exactly change is needed ?

  Ummm. . .

  Cadavre (12/21/2011)

  ---

  My random guess: -

  DECLARE @CurrentDate DATETIME --= Fireball_Configuration.dbo.PreviousBusinessDay()

  SELECT @CurrentDate = MAX(DATE)

  FROM Fireball_Reporting..Reporting_LoanPrices

  DECLARE @CompareDate DATETIME = Fireball_Configuration.dbo.PreviousBusinessDayByDate (@CurrentDate)

  DECLARE @count INT = 5

  DECLARE @EmailHeader VARCHAR(500) = Fireball_Configuration.dbo.GetConfigurationValue ('Fireball', 'EmailSettings - Header')

  DECLARE @EmailFooter VARCHAR(500) = Fireball_Configuration.dbo.GetConfigurationValue ('Fireball', 'EmailSettings - Footer')

  DECLARE @tableHTML VARCHAR(MAX)

  SET @tableHTML = @tableHTML + '<H1>Top ' + CONVERT(VARCHAR(20), @COUNT) + ' Distressed Losers</H1>' +

  '<TABLE border = "1" cellspacing="0" cellpadding="5">' + '<TR><TH>Company</TH><TH>

  Security</TH><TH>Current Date</TH><TH>Current Price</TH><TH>Compare Date</TH><TH>

  Compare Price</TH><TH>Delta</TH></TR>' + CAST((

  SELECT TD = CONVERT(VARCHAR(20), CompanyName), '', TD = CONVERT(VARCHAR(20), SecurityName),

  '', TD = CONVERT(VARCHAR(20), CurentDate), '', TD = CONVERT(VARCHAR(20), CurrentPrice), '',

  TD = CONVERT(VARCHAR(20), PreviousDate), '', TD = CONVERT(VARCHAR(20), PreviousPrice), '',

  TD = CONVERT(VARCHAR(20), Delta)

  FROM (

  SELECT TOP (@count) CONVERT(VARCHAR(10), CurrentLoans.DATE, 101) AS CurentDate,

  CONVERT(VARCHAR(10), PreviousLoans.DATE, 101) AS PreviousDate, CurrentLoans.CompanyName,

  CurrentLoans.SecurityName, CONVERT(VARCHAR(20), CurrentLoans.SecurityId) AS CurrentSecID,

  CONVERT(VARCHAR(20), PreviousLoans.SecurityId) AS PreviousSecID,

  CONVERT(VARCHAR(20), CurrentLoans.Price) AS CurrentPrice,

  CONVERT(VARCHAR(20), PreviousLoans.Price) AS PreviousPrice,

  CONVERT(VARCHAR(8), CONVERT(DECIMAL(8, 4), ((CurrentLoans.Price - PreviousLoans.Price) / PreviousLoans.Price) * 100)) + '%' AS Delta

  FROM (

  SELECT LoanPrices.DATE, LoanPrices.SecurityId, Company.NAME CompanyName, Security.NAME SecurityName, (LoanPrices.Bid + LoanPrices.Ask) / 2 Price

  FROM Fireball_Reporting..Reporting_LoanPrices LoanPrices

  INNER JOIN Fireball..Security ON Security.SecurityId = LoanPrices.SecurityId

  INNER JOIN Fireball..Company ON Company.CompanyId = Security.CompanyId

  ) CurrentLoans

  INNER JOIN (

  SELECT LoanPrices.DATE, LoanPrices.SecurityId, Company.NAME CompanyName, Security.NAME SecurityName, (LoanPrices.Bid + LoanPrices.Ask) / 2 Price

  FROM Fireball_Reporting..Reporting_LoanPrices LoanPrices

  INNER JOIN Fireball..Security ON Security.SecurityId = LoanPrices.SecurityId

  INNER JOIN Fireball..Company ON Company.CompanyId = Security.CompanyId

  ) PreviousLoans ON PreviousLoans.SecurityId = CurrentLoans.SecurityId AND PreviousLoans.DATE = @CompareDate

  WHERE CurrentLoans.DATE = @CurrentDate AND CurrentLoans.Price <> PreviousLoans.Price AND CurrentLoans.Price < 70

  ORDER BY (CurrentLoans.Price - PreviousLoans.Price) / PreviousLoans.Price ASC

  ) Movers

  FOR XML PATH('TR'), TYPE

  ) AS VARCHAR(MAX)) + '</TABLE>' + @EmailFooter;

  Without DDL, it's pretty much impossible to be sure though.

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• ashuthinks

  SSCommitted

  Points: 1698

  More actions

  December 21, 2011 at 8:55 am

  #1424301

  I tried yours 🙁 fail same error 🙁

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  December 21, 2011 at 9:24 am

  #1424326

  Try this

  DECLARE @CurrentDate DATETIME --= Fireball_Configuration.dbo.PreviousBusinessDay()

  SELECT @CurrentDate = MAX(DATE)

  FROM Fireball_Reporting..Reporting_LoanPrices

  DECLARE @CompareDate DATETIME = Fireball_Configuration.dbo.PreviousBusinessDayByDate (@CurrentDate)

  DECLARE @count INT = 5

  DECLARE @EmailHeader VARCHAR(500) = Fireball_Configuration.dbo.GetConfigurationValue ('Fireball', 'EmailSettings - Header')

  DECLARE @EmailFooter VARCHAR(500) = Fireball_Configuration.dbo.GetConfigurationValue ('Fireball', 'EmailSettings - Footer')

  DECLARE @tableHTML VARCHAR(MAX)

  SET @tableHTML = @tableHTML + '<H1>Top ' + CONVERT(VARCHAR(20), @COUNT) + ' Distressed Losers</H1>' +

  '<TABLE border = "1" cellspacing="0" cellpadding="5">' + '<TR><TH>Company</TH><TH>

  Security</TH><TH>Current Date</TH><TH>Current Price</TH><TH>Compare Date</TH><TH>

  Compare Price</TH><TH>Delta</TH></TR>' + CAST((

  SELECT TD = CONVERT(VARCHAR(20), CompanyName), '', TD = CONVERT(VARCHAR(20), SecurityName),

  '', TD = CONVERT(VARCHAR(20), CurentDate), '', TD = CONVERT(VARCHAR(20), CurrentPrice), '',

  TD = CONVERT(VARCHAR(20), PreviousDate), '', TD = CONVERT(VARCHAR(20), PreviousPrice), '',

  TD = CONVERT(VARCHAR(20), Delta)

  FROM (

  SELECT TOP (@count) CONVERT(VARCHAR(10), CurrentLoans.DATE, 101) AS CurentDate,

  CONVERT(VARCHAR(10), PreviousLoans.DATE, 101) AS PreviousDate, CurrentLoans.CompanyName,

  CurrentLoans.SecurityName, CONVERT(VARCHAR(20), CurrentLoans.SecurityId) AS CurrentSecID,

  CONVERT(VARCHAR(20), PreviousLoans.SecurityId) AS PreviousSecID,

  CONVERT(VARCHAR(20), CurrentLoans.Price) AS CurrentPrice,

  CONVERT(VARCHAR(20), PreviousLoans.Price) AS PreviousPrice,

  CONVERT(VARCHAR(15), CONVERT(DECIMAL(8, 4), ((CurrentLoans.Price - PreviousLoans.Price) / PreviousLoans.Price) * 100)) + '%' AS Delta

  FROM (

  SELECT LoanPrices.DATE, LoanPrices.SecurityId, Company.NAME CompanyName, Security.NAME SecurityName, (LoanPrices.Bid + LoanPrices.Ask) / 2 Price

  FROM Fireball_Reporting..Reporting_LoanPrices LoanPrices

  INNER JOIN Fireball..Security ON Security.SecurityId = LoanPrices.SecurityId

  INNER JOIN Fireball..Company ON Company.CompanyId = Security.CompanyId

  ) CurrentLoans

  INNER JOIN (

  SELECT LoanPrices.DATE, LoanPrices.SecurityId, Company.NAME CompanyName, Security.NAME SecurityName, (LoanPrices.Bid + LoanPrices.Ask) / 2 Price

  FROM Fireball_Reporting..Reporting_LoanPrices LoanPrices

  INNER JOIN Fireball..Security ON Security.SecurityId = LoanPrices.SecurityId

  INNER JOIN Fireball..Company ON Company.CompanyId = Security.CompanyId

  ) PreviousLoans ON PreviousLoans.SecurityId = CurrentLoans.SecurityId AND PreviousLoans.DATE = @CompareDate

  WHERE CurrentLoans.DATE = @CurrentDate AND CurrentLoans.Price <> PreviousLoans.Price AND CurrentLoans.Price < 70

  ORDER BY (CurrentLoans.Price - PreviousLoans.Price) / PreviousLoans.Price ASC

  ) Movers

  FOR XML PATH('TR'), TYPE

  ) AS VARCHAR(MAX)) + '</TABLE>' + @EmailFooter;

  You're converting a decimal(8,4) to a varchar(8) which isn't enough room.

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• TedT

  Right there with Babe

  Points: 796

  More actions

  December 21, 2011 at 9:25 am

  #1424327

  Post the create statements for your tables, and then we'll have a better chance of helping! 🙂

  _____________________________________________________________________
  Disclaimer - The opinions expressed by the mouth are not necessarily those of the brain