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

Question

Post reply

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

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

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

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 · Answer 2

ashuthinks

SSCommitted

Points: 1698

December 21, 2011 at 8:51 am

#1424299

where exactly change is needed ?

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

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 · Answer 4

ashuthinks

SSCommitted

Points: 1698

December 21, 2011 at 8:55 am

#1424301

I tried yours 🙁 fail same error 🙁

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

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 · Answer 6

TedT

Right there with Babe

Points: 796

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