March 13, 2009 at 8:45 pm
Can anyone tell me why this statement works on one server (dev) but sometimes blows up on another (qa)? (Error converting data type nvarchar to numeric.)
UPDATE @tblReportDetail
SET Measure =
CASE
WHEN @MeasureSpecial = 0 THEN CAST(ddf.FieldValue AS decimal(20,2))
WHEN @MeasureName = 'Hard Cost' THEN it.HardCost
WHEN @MeasureName = 'Soft Cost' THEN it.SoftCost
WHEN @MeasureName = 'RVU' THEN it.RVU
END
FROM blah blah blah...
I know it's the CASE stmt because if i replace it with a 0 it works fine. The Measure field is decimal(20,2), and so are HardCost, SoftCost and RVU.
March 13, 2009 at 9:34 pm
(Error converting data type nvarchar to numeric.)
UPDATE @tblReportDetail
SET Measure =
CASE
WHEN @MeasureSpecial = 0 THEN CAST(ddf.FieldValue AS decimal(20,2))
WHEN @MeasureName = 'Hard Cost' THEN it.HardCost
WHEN @MeasureName = 'Soft Cost' THEN it.SoftCost
WHEN @MeasureName = 'RVU' THEN it.RVU
END
You are having to CAST ddf.FieldValue as decimal(20,2) so I'm thinking ddf.FieldValue is most likely an nvarchar column, right?. Odds are there is some non-numeric data in the QA system database in that column.
You can use the ISNUMERIC function to query the QA data and find any values that are going to make the CAST statement throw that error. You could also add either of the following to your UPDATE query.
WHERE ISNUMERIC(ddf.FieldValue) = 1
or
WHEN @MeasureSpecial = 0 and ISNUMERIC(ddf.FieldValue) = 1 THEN CAST(ddf.FieldValue AS decimal(20,2))
WHEN @MeasureSpecial = 0 and ISNUMERIC(ddf.FieldValue) = 0 THEN NULL -- or zero, or whatever
Let me know if this helps, or if you have any questions.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 13, 2009 at 9:44 pm
I think you're right, some numeric default values were changed (to empty string). I'll add the code you suggest to set to zero in the event that value is not numeric. Thanks!
March 13, 2009 at 10:07 pm
You're welcome. Have a good weekend.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 16, 2009 at 6:52 am
Just to be safe you should read this thread where it is pointed out that IsNumeric can return true in some odd cases like "+" and currency symbols.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 16, 2009 at 7:14 am
Owch, I forgot all about that. You're exactly right, Jack. Good save, and thank you.
The thread is definitely worth reading in it's entirety, because the test you build depends entirely on how you want to cast your data: numeric, float, integer, etc.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply