显示将varchar的错误转换为数字

I am getting this error

我收到了这个错误

Msg 8114, Level 16, State 5, Line 13
Error converting data type varchar to numeric.

消息8114,级别16,状态5,行13错误将数据类型varchar转换为数字。

This is my code:

这是我的代码:

SELECT DISTINCT  
                      DI.DealName AS 'Deal Name',  
    DI.OfferingType AS 'Offering Type',   
    DI.Date AS 'Date',  
    DI.ClassName AS 'Class',  
    ISNULL(Bench,'-') AS 'BM',  
    ISNULL(Guidance,'-') AS 'Guid',  
    DI.Industry AS 'Industry',  
    CAST( (ISNULL(DI.AverageLife,0)) AS VARCHAR(20)) AS 'WAL',CAST(  ISNULL(DI.ClassSize,0) AS VARCHAR(20)) AS 'Balance',CAST(   REPLACE(ISNULL(DI.Coupon,0),'%','') AS VARCHAR(20)) AS 'Coupon',  
       CAST(  CASE WHEN CAST(REPLACE(ISNULL(DI.PX2,0),'%','') AS DECIMAL(18,5)) = 0.00 THEN (CASE WHEN ISNULL(CAST(DI.Yield * 100 AS DECIMAL(18,3)),0) = 0 THEN (CASE WHEN (ISNULL(DI.Coupon,0)+'cpn' = 'cpn' OR ISNULL(DI.Coupon,0)+'cpn' = '0cpn') THEN '-' ELSE (ISNULL(DI.Coupon,0)+'cpn')END)ELSE CAST(ISNULL(CAST(DI.Yield * 100 AS DECIMAL(18,3)),0) AS VARCHAR(15))+'%yld' END) ELSE (ISNULL(DI.PX,0)) END AS VARCHAR(20)) AS 'Spread',  CAST(  ISNULL(CAST(DI.Yield * 100 AS DECIMAL(18,3)),0) AS VARCHAR(20)) AS 'Yield', CAST(  CASE WHEN LTRIM(RTRIM(DI.Dollar_Px)) = '' THEN 0.00 ELSE CAST(ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(Dollar_Px)),CHAR(10),''),CHAR(13),''),'-',''),'/',''),' ',''),'%cpn',''),0) AS DECIMAL(18,5)) END AS VARCHAR(20)) AS 'Dollar_Px',  ISNULL(Notes,'') AS Notes,                 
           CAST((FRM.Rating + '/' + KBRARM.Rating) AS VARCHAR(15)) AS 'Rating'  FROM DI_DealIssuancePost DI  
         INNER JOIN DI_CompanyRatingMapping MRM ON MRM.CompanyRatingMappingID = DI.Mdy                   INNER JOIN DI_CompanyRatingMapping SPRM ON SPRM.CompanyRatingMappingID = DI.S_P  
         INNER JOIN DI_CompanyRatingMapping FRM ON FRM.CompanyRatingMappingID = DI.Fitch  
         INNER JOIN DI_CompanyRatingMapping DBRSRM ON DBRSRM.CompanyRatingMappingID = DI.DBRS                 INNER JOIN DI_CompanyRatingMapping KBRARM ON KBRARM.CompanyRatingMappingID = DI.KBRA  

         INNER JOIN DI_WeeklySectors DIWeekly ON DI.Industry = DIWeekly.Sector  

        WHERE DI.IsDeleted = 0              
          AND MRM.IsDeleted = 0  
          AND SPRM.IsDeleted = 0  
          AND FRM.IsDeleted = 0  
          AND DBRSRM.IsDeleted = 0  
          AND KBRARM.IsDeleted = 0  
          AND DI.Date IS NOT NULL   
          AND (CONVERT(DATETIME,DI.Date ,101) >= CONVERT(DATETIME,@StartDate,101))   
          AND (CONVERT(DATETIME,DI.Date ,101) <= CONVERT(DATETIME,@EndDate,101)) AND DI.CurrencyID IN (SELECT * FROM CsvToTable(@Currency)) ORDER BY DI.Date desc

Msg 8114, Level 16, State 5, Line 13 Error converting data type varchar to numeric.

消息8114,级别16,状态5,行13错误将数据类型varchar转换为数字。

And i Can find that here is an error

我可以发现这是一个错误

 CAST(  CASE WHEN CAST(REPLACE(ISNULL(DI.PX2,0),'%','') AS DECIMAL(18,5)) = 0.00 THEN (CASE WHEN ISNULL(CAST(DI.Yield * 100 AS DECIMAL(18,3)),0) = 0 THEN (CASE WHEN (ISNULL(DI.Coupon,0)+'cpn' = 'cpn' OR ISNULL(DI.Coupon,0)+'cpn' = '0cpn') THEN '-' ELSE (ISNULL(DI.Coupon,0)+'cpn')END)ELSE CAST(ISNULL(CAST(DI.Yield * 100 AS DECIMAL(18,3)),0) AS VARCHAR(15))+'%yld' END) ELSE (ISNULL(DI.PX,0)) END AS VARCHAR(20)) AS 'Spread',  CAST(  ISNULL(CAST(DI.Yield * 100 AS DECIMAL(18,3)),0) AS VARCHAR(20)) AS 'Yield', CAST(  CASE WHEN LTRIM(RTRIM(DI.Dollar_Px)) = '' THEN 0.00 ELSE CAST(ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(Dollar_Px)),CHAR(10),''),CHAR(13),''),'-',''),'/',''),' ',''),'%cpn',''),0) AS DECIMAL(18,5)) END AS VARCHAR(20)) AS 'Dollar_Px',  ISNULL(Notes,'') AS Notes,

some one help to solve this error

有人帮助解决这个错误

2 个解决方案

#1

Check below one by one:

请逐一查看以下内容:

SELECT ISNUMERIC(ISNULL(DI.PX2,0)) FROM DI WHERE ISNUMERIC(ISNULL(DI.PX2,0)) = 0

SELECT ISNUMERIC(ISNULL(DI.Yield,0)) FROM DI WHERE ISNUMERIC(ISNULL(DI.Yield,0)) = 0

SELECT ISNUMERIC(ISNULL(Dollar_Px,0)) FROM TABLE WHERE ISNUMERIC(ISNULL(Dollar_Px,0)) = 0

There must exist any varchar string that cannot be converted to decimal

必须存在任何无法转换为十进制的varchar字符串

#2

Here is an error:

这是一个错误:

(ISNULL(DI.Coupon,0)+''cpn'' = ''cpn'' OR ISNULL(DI.Coupon,0)+''cpn'' = ''0c

pn'') THEN

Your Coupon is varchar, but if it's NULL you use 0 instead of it. You should use '0' as it's string, not integer, in your '0cpn'.

您的优惠券是varchar,但如果它是NULL,则使用0而不是它。您应该在'0cpn'中使用'0'作为字符串,而不是整数。

So it should be

所以它应该是

(ISNULL(DI.Coupon,''0'')+''cpn'' = ''cpn'' OR ISNULL(DI.Coupon,''0'')+''cpn'' = ''0c

pn'') THEN

#1