January 25, 2017 at 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
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:
I would appreciate any help.
January 25, 2017 at 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.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 25, 2017 at 8:49 am
lskidgel - Wednesday, January 25, 2017 8:38 AMI 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
January 25, 2017 at 9:20 am
-- ===============================================================
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
January 25, 2017 at 9:22 am
Thom A - Wednesday, January 25, 2017 8:48 AMThe 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.
January 25, 2017 at 9:37 am
lskidgel - Wednesday, January 25, 2017 8:38 AMNot 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
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
January 25, 2017 at 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.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 25, 2017 at 12:16 pm
Thom A - Wednesday, January 25, 2017 9:44 AMSeeing 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.
January 25, 2017 at 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 '%.%.%');
January 25, 2017 at 2:36 pm
Luis Cazares - Wednesday, January 25, 2017 1:45 PMIs 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?
January 26, 2017 at 6:51 am
lskidgel - Wednesday, January 25, 2017 2:36 PMLuis Cazares - Wednesday, January 25, 2017 1:45 PMIs 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?
Also, if FormID is a uniqueidentifier, why don't you set your parameter as such?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply