Khắc phục lỗi converting data type nvarchar to numeric năm 2024

It is the GrandTotalAmount (which is not a column in the db) I am having issues with. I have read many forum topics for this issue but cannot seem to get this to work. I cannot unfortunately make changes to the database. What I have tried so far:

• Case when isnumeric(Qty.primaryanswer) = 1 then cast(Qty.PrimaryAnswer as int) else 0 end * Case when isnumeric(WC.primaryanswer) = 1 then cast(WC.PrimaryAnswer as decimal (18,2)) else 0 end
• Cast(Qty.primaryanswer as decimal (8,2)) * Cast(WC.primaryanswer as decimal (8,2))
• Cast(Qty.primaryanswer as numeric ) * Cast(WC.primaryanswer as numeric)
• Convert(decimal (8,2),Qty.primaryanswer) * Convert(decimal (8,2),WC.primaryanswer)

I would appreciate any help.

Thom A

SSC Guru

Points: 99125

The problem is that one of your columns contains a value that cannot be converted to a numeric. Your attempts above won't solve this (in this case stating a conversion as well), as you'r just trying to push harder. It doesn't matter how hard you try to push a square peg through a circular hole, if it doesn't fit, it's not going in.

Using a VARCHAR to store numerics isn't a great idea, as it means someone could happily store the value "2,134", which could cause SQL query to fall over, but could also just as easily put "seven" (which is even worse). If you're stoing numerics, then you should really use a column type to reflect this.

Select from your data WHERE ISNUMERIC(Qty.PrimaryAnswer) = 0 OR ISNUMERIC(WC.PrimaryAnswer) = 0, this'll give you your bad rows (don't try to do the maths, just display it). Should this data then be eliminated from your dataset, or fixed at source? If it should be fixed, pursue getting it fixed. If not, you'll need to exclude it from your dataset before attempting to treat a varchar like a numeric and trusting it's all valid.

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

lskidgel - Wednesday, January 25, 2017 8:38 AM

I am at my wits end. I have a stored procedure that gives me the error converting data type nvarchar to numeric. Qty = Qty.PrimaryAnswer ,WorkCost = WC.PrimaryAnswer ,GrandTotalAmount = Qty.PrimaryAnswer * WC.PrimaryAnswer

Case when isnumeric(Qty.primaryanswer) = 1 then cast(Qty.PrimaryAnswer as int) else 0 end \ Case when isnumeric(WC.primaryanswer) = 1 then cast(WC.PrimaryAnswer as decimal (18,2)) else 0 end Cast(Qty.primaryanswer as decimal (8,2)) \ Cast(WC.primaryanswer as decimal (8,2)) Cast(Qty.primaryanswer as numeric ) \ Cast(WC.primaryanswer as numeric) Convert(decimal (8,2),Qty.primaryanswer) \ Convert(decimal (8,2),WC.primaryanswer) I would appreciate any help.

Please post the full query, along with table DDL in the form of CREATE TABLE statement(s), sample data in the form of INSERT statements and your expected results based on the sample data. You've posted in the SQL Server 2008 forum, but if you happen to have SQL Server 2012, you could use TRY_CONVERT. I don't advise you to use ISNUMERIC - it can give unexpected results.

John

lskidgel

SSC-Addicted

Points: 456

John Mitchell-245523 - Wednesday, January 25, 2017 8:49 AM

lskidgel - Wednesday, January 25, 2017 8:38 AM

Not sure what you mean by the ...the table DDL... But column - PrimaryAnswer (nvarchar(1850),null) Below is the 'original' sproc that actually works in 2012, but when run in 2008r2 (which it has to be), it fails. The datatypes are nThe results I expect to get back, as example Qty.PrimaryAnswer [2] WC.PrimaryAnswer [22.50] The GrandTotalAmount would be the value when the 2 above are multiplied. GO /****** Object: StoredProcedure [dbo].[Rpt_Invoice] Script Date: 1/25/2017 8:55:45 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

-- =============================================================== ALTER PROCEDURE [dbo].[Rpt_Invoice] @FormID as nvarchar(36) ,@JobInstanceID as nvarchar(36) AS BEGIN

Select Qty = Qty.PrimaryAnswer ,WorkDescription = WD.PrimaryAnswer ,WorkCost = WC.PrimaryAnswer ,SiteUserField4 = cast(JS.UserField4 as decimal (18,4)) ,GrandTotalAmount = Convert(Money,Qty.primaryanswer) * Convert(Money,WC.primaryanswer) from Form F Join FormItems FI on FI.FormFK = F.ID Join dbo.FormSection FS on FS.FormItemsFk = FI.ID JOIN dbo.JobItems Jitms ON FI.id = Jitms.FormItemsFk Join Jobinstance JI ON Jitms.JobInstanceFk = JI.ID and JI.ID = @JobInstanceID Join JobMaster JM on JM.ID = JI.JobMasterFk Join Site JS on JS.ID = Jm.SiteFk --Work Qty Join FormQuestion FQQty ON FQQty.FormSectionFk = FS.ID and FQQty.AltFieldName = 'Qty' Join Answers Qty ON (Qty.JobItemsFk = Jitms.id and Qty.FormQuestionFk = FQQty.ID) --Work Description and Detail Join FormQuestion FQWD ON FQWD.FormSectionFk = FS.ID and FQWD.AltFieldName = 'DescriptionWork' Join Answers WD ON (WD.JobItemsFk = Jitms.id and WD.FormQuestionFk = FQWD.ID) --Work Description cost Join FormQuestion FQWC ON FQWC.FormSectionFk = FS.ID and FQWC.AltFieldName = 'WorkCost' Join Answers WC ON (WC.JobItemsFk = Jitms.id and WC.FormQuestionFk = FQWC.ID)

where F.ID = @FormID

End

lskidgel

SSC-Addicted

Points: 456

Thom A - Wednesday, January 25, 2017 8:48 AM

The problem is that one of your columns contains a value that cannot be converted to a numeric. Your attempts above won't solve this (in this case stating a conversion as well), as you'r just trying to push harder. It doesn't matter how hard you try to push a square peg through a circular hole, if it doesn't fit, it's not going in.Using a VARCHAR to store numerics isn't a great idea, as it means someone could happily store the value "2,134", which could cause SQL query to fall over, but could also just as easily put "seven" (which is even worse). If you're stoing numerics, then you should really use a column type to reflect this.

Select from your data WHERE ISNUMERIC(Qty.PrimaryAnswer) = 0 OR ISNUMERIC(WC.PrimaryAnswer) = 0, this'll give you your bad rows (don't try to do the maths, just display it). Should this data then be eliminated from your dataset, or fixed at source? If it should be fixed, pursue getting it fixed. If not, you'll need to exclude it from your dataset before attempting to treat a varchar like a numeric and trusting it's all valid.

Thank you for your reply, and I agree the columns should be other, but it cannot be changed.

John Mitchell-245523

SSC Guru

Points: 148809

lskidgel - Wednesday, January 25, 2017 9:20 AM

John Mitchell-245523 - Wednesday, January 25, 2017 8:49 AM

lskidgel - Wednesday, January 25, 2017 8:38 AM

Not sure what you mean by the ...the table DDL... But column - PrimaryAnswer (nvarchar(1850),null) Below is the 'original' sproc that actually works in 2012, but when run in 2008r2 (which it has to be), it fails. The datatypes are nThe results I expect to get back, as example Qty.PrimaryAnswer [2] WC.PrimaryAnswer [22.50] The GrandTotalAmount would be the value when the 2 above are multiplied. GO /****** Object: StoredProcedure [dbo].[Rpt_Invoice] Script Date: 1/25/2017 8:55:45 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

-- =============================================================== ALTER PROCEDURE [dbo].[Rpt_Invoice] @FormID as nvarchar(36) ,@JobInstanceID as nvarchar(36) AS BEGIN

Select Qty = Qty.PrimaryAnswer ,WorkDescription = WD.PrimaryAnswer ,WorkCost = WC.PrimaryAnswer ,SiteUserField4 = cast(JS.UserField4 as decimal (18,4)) ,GrandTotalAmount = Convert(Money,Qty.primaryanswer) * Convert(Money,WC.primaryanswer) from Form F Join FormItems FI on FI.FormFK = F.ID Join dbo.FormSection FS on FS.FormItemsFk = FI.ID JOIN dbo.JobItems Jitms ON FI.id = Jitms.FormItemsFk Join Jobinstance JI ON Jitms.JobInstanceFk = JI.ID and JI.ID = @JobInstanceID Join JobMaster JM on JM.ID = JI.JobMasterFk Join Site JS on JS.ID = Jm.SiteFk --Work Qty Join FormQuestion FQQty ON FQQty.FormSectionFk = FS.ID and FQQty.AltFieldName = 'Qty' Join Answers Qty ON (Qty.JobItemsFk = Jitms.id and Qty.FormQuestionFk = FQQty.ID) --Work Description and Detail Join FormQuestion FQWD ON FQWD.FormSectionFk = FS.ID and FQWD.AltFieldName = 'DescriptionWork' Join Answers WD ON (WD.JobItemsFk = Jitms.id and WD.FormQuestionFk = FQWD.ID) --Work Description cost Join FormQuestion FQWC ON FQWC.FormSectionFk = FS.ID and FQWC.AltFieldName = 'WorkCost' Join Answers WC ON (WC.JobItemsFk = Jitms.id and WC.FormQuestionFk = FQWC.ID)

where F.ID = @FormID

End

Table DDL.

Could it be that the reason it works in 2012 is that the data is cleaner on that server?

Are you sure the conversion error isn't occurring on UserField4? Either way, since you can't change the database design, you're going to have to go through the offending column and identify the unconvertible data, then decide what you're going to do with it. What Thom posted is a very good place to start.

John

Thom A

SSC Guru

Points: 99125

Seeing the full query now means that there's a lot of places this could be falling over. Although I said start with the above columns, upon seeing the above, you have a much bigger amount of columns that you could be dealing with with the problem. Without knowing the DDL I can't tell you where to start, but if my above example yields no results look for JOINs where you are comparing a Numeric value to a varchar.

Thom~

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

lskidgel

SSC-Addicted

Points: 456

Thom A - Wednesday, January 25, 2017 9:44 AM Seeing the full query now means that there's a lot of places this could be falling over. Although I said start with the above columns, upon seeing the above, you have a much bigger amount of columns that you could be dealing with with the problem. Without knowing the DDL I can't tell you where to start, but if my above example yields no results look for JOINs where you are comparing a Numeric value to a varchar.

Thanks Thom for the reply. I will continue to investigate.

Luis Cazares

SSC Guru

Points: 183697

Is Form.ID an nvarchar column or a numeric? If it's a numeric, why are you using nvarchar for the parameter? The values in Qty.primaryanswer and WC.primaryanswer include the braquets? Or you just include them for reference? Do you get any results from this query? SELECT * FROM FormQuestion FQ Join Answers A ON A.FormQuestionFk = FQ.ID WHERE FQ.AltFieldName IN ('WorkCost', 'Qty') AND (A.primaryanswer LIKE '%[^0-9.]%' OR A.primaryanswer LIKE '%.%.%');

Luis C. General Disclaimer: Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

lskidgel

SSC-Addicted

Points: 456

Luis Cazares - Wednesday, January 25, 2017 1:45 PM

Is Form.ID an nvarchar column or a numeric? If it's a numeric, why are you using nvarchar for the parameter? The values in Qty.primaryanswer and WC.primaryanswer include the braquets? Or you just include them for reference? Do you get any results from this query?

SELECT * FROM FormQuestion FQ Join Answers A ON A.FormQuestionFk = FQ.ID WHERE FQ.AltFieldName IN ('WorkCost', 'Qty') AND (A.primaryanswer LIKE '%[^0-9.]%' OR A.primaryanswer LIKE '%.%.%');

FormID is a uniqueidentifier, necessary in the way the sproc runs, but has no bearing on the issue I am having. The values expected back from Qty.primaryanswer and WC.primaryanswer will only be numbers, possibly decimals as in 2.50. I did not get any results back from your query until I removed the decimal. So, based on this test, I should be able to convert my results into a decimal, is that right? If so, where am I going wrong?

Luis Cazares

SSC Guru

Points: 183697

lskidgel - Wednesday, January 25, 2017 2:36 PM

Luis Cazares - Wednesday, January 25, 2017 1:45 PM

Is Form.ID an nvarchar column or a numeric? If it's a numeric, why are you using nvarchar for the parameter? The values in Qty.primaryanswer and WC.primaryanswer include the braquets? Or you just include them for reference? Do you get any results from this query?

SELECT * FROM FormQuestion FQ Join Answers A ON A.FormQuestionFk = FQ.ID WHERE FQ.AltFieldName IN ('WorkCost', 'Qty') AND (A.primaryanswer LIKE '%[^0-9.]%' OR A.primaryanswer LIKE '%.%.%');

FormID is a uniqueidentifier, necessary in the way the sproc runs, but has no bearing on the issue I am having. The values expected back from Qty.primaryanswer and WC.primaryanswer will only be numbers, possibly decimals as in 2.50. I did not get any results back from your query until I removed the decimal. So, based on this test, I should be able to convert my results into a decimal, is that right? If so, where am I going wrong?

If the original query didn't return results, the problem might be in one of the joins. Do you have anything that joins a numeric with a string?