执行以下查询时收到错误消息

Question

SELECT o.TypeAbv + ' (' + o.TypeCode + ')' AS 'Type', o.PracticeAbv + ' (' + o.PracticeCode + ')' 
AS 'Practice', b.Unit AS 'Unit #', o.Acres AS 'Acres', o.InsuredSharePct AS 'Share', o.ApprovedYield 
AS 'Approved Yield', o.AdjustedYield AS 'Adjusted Yield', o.ProdGuarantee AS 'Guar/Acre', o.RateYield 
AS 'Simple Avg Yield', 
(CAST((CASE WHEN o.ProdGuarantee = '' THEN Null ELSE o.ProdGuarantee END) as decimal(10,2)) * CAST(o.Acres as decimal(10,2))) 
AS 'Total Guar UOM', o.BasePrice AS 'Price', o.Liability AS 'Total Protection', o.UnitDiscountFactor 
AS 'Unit Disc Factor', o.TotalPremium AS 'Base Premium', o.ProdPremium AS 'Subsidy Premium', o.Subsidy 
AS 'Subsidy Amount', o.SubsidyPercent AS 'Subsidy %', o.PremiumRate AS 'Base Prem Rate' 
FROM RMA_Output o INNER JOIN AR_Premium_Baseline b ON b.TestCaseID = o.TestCaseID 
AND b.ModuleNumber = o.ModuleNumber AND b.CropLineNumber = o.CropLineNumber AND b.ARLineNumber = o.ARLineNumber 
WHERE o.TestCaseID = 'AR_CA_Sacmnto_Chry_ARH_FeeByGrp_OuBuElection_MP4006' AND o.ModuleNumber = 4 AND o.CropLineNumber = 1 
AND o.CropYear = 2018
ORDER BY b.Unit, o.PracticeCode, o.TypeAbv

错误消息： Msg 8114，级别 16，状态 5，第 32 行 将数据类型 nvarchar 转换为数字时出错。

有人有解决方法吗？

Answer 1

看起来这是您进行转换的唯一行。

 ,(CAST((CASE WHEN o.ProdGuarantee = '' THEN Null ELSE o.ProdGuarantee END) as decimal(10,2)) * CAST(o.Acres as decimal(10,2))) AS 'Total Guar UOM'

如果是 2012+，请使用 TRY_CONVERT((

 ,try_convert(decimal(10,2),o.ProdGuarantee) * try_convert(decimal(10,2),o.Acres) AS 'Total Guar UOM'

如果转换失败，Try_Convert(( 将返回 NULL，并且不会引发错误。