converting a blank to numeric

Question

Post reply

converting a blank to numeric

GrassHopper

SSCarpal Tunnel

Points: 4465
More actions
January 29, 2018 at 12:46 pm

#326630

I have this query below that runs and just gave me an error msg "Error converting data type varchar to numeric." The error is due to a blank value in TotalAdj column. How can I check for blanks and make them 0 on the fly so that the query below does not bomb out on me?
Select Sum(Cast(amtAdj as Numeric(14,2))) as AmtAdj_Sum, Max(Cast(TotalAdj as Numeric(14,2))) as TotalAdj, count(*) as cnt, InvAdjNum, InvoiceNo, VendorNo, CBRVend
INTO [QA_Collection].[dbo].[INVADJ_MAY_JUN_AUG_SEP_OCT_NOV_ALL_Sum]
FROM [QA_Collection].[dbo].[INVADJ_MAY_JUN_AUG_SEP_OCT_NOV_ALL]
where rtrim(ltrim(amtadj)) <> ''
Group by InvAdjNum, InvoiceNo, VendorNo, CBRVend
Order by InvAdjNum

Viewing 4 posts - 1 through 3 (of 3 total)

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

drew.allen SSC Guru Points: 76944 More actions · Answer 1

Since you're on SQL 2012, you can use TRY_CAST() instead of just CAST().

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

LinksUp SSCertifiable Points: 6526 More actions · Answer 2

GrassHopper - Monday, January 29, 2018 12:46 PM
I have this query below that runs and just gave me an error msg "Error converting data type varchar to numeric." The error is due to a blank value in TotalAdj column. How can I check for blanks and make them 0 on the fly so that the query below does not bomb out on me?
Select Sum(Cast(amtAdj as Numeric(14,2))) as AmtAdj_Sum, Max(Cast(TotalAdj as Numeric(14,2))) as TotalAdj, count(*) as cnt, InvAdjNum, InvoiceNo, VendorNo, CBRVend
INTO [QA_Collection].[dbo].[INVADJ_MAY_JUN_AUG_SEP_OCT_NOV_ALL_Sum]
FROM [QA_Collection].[dbo].[INVADJ_MAY_JUN_AUG_SEP_OCT_NOV_ALL]
where rtrim(ltrim(amtadj)) <> ''
Group by InvAdjNum, InvoiceNo, VendorNo, CBRVend
Order by InvAdjNum

First question would be why amtAdj and TotalAdj are not a numeric base type to begin with?

Try a case statement:
MAX(CAST(case when TotalAdj = '' then '0' else TotalAdj end as Numeric(14,2)))

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

GrassHopper SSCarpal Tunnel Points: 4465 More actions · Answer 3

drew.allen - Monday, January 29, 2018 1:21 PM
Since you're on SQL 2012, you can use TRY_CAST() instead of just CAST().
Drew

forgot about the try_cast(), i'm actually in 2016. Worked, thanks!