sql - 在 sql server 2008 中将数据类型 varchar 转换为数字时出错

Question

我在 Sql server 中运行一个查询并收到错误“将数据类型 varchar 转换为数字时出错”。请在下面找到我正在查询数据库的查询。

SELECT   Limit = (Case isnull(PR009000.COVERAGE ,null)
     When isnull('UM',null) then        
        (Case
            When 'SPLIT' = 'CSL' then  ( select UMLimitTypeDes from CASeleUMLimitType where UMCoverageTypeCd =( 'SPLIT' )  
                 and UMLimitTypeCd=(Select top 1 USAMNT02 From PR006000 Where Symbol= 'CPP' and POLNUM = '0001148' and MODULE = '0 ' and MCO = '05' and LOC= '00' and INSLINE = 'CA' )
                 and StateCd= (select top 1 RATESTE  from PR009000 where SYMBOL='CPP' AND POLNUM=0001148 AND MODULE=00 AND LOC=00 AND MCO=05 AND PCO=05 AND UNTRECNUM=1) )
            When 'SPLIT' = 'SL' then (select UMLimitTypeDes from CASeleUMLimitType where UMCoverageTypeCd=( 'SPLIT' )  
                 and UMLimitTypeCd=(Select top 1 USAMNT02 From PR006000 Where Symbol= 'CPP' and POLNUM = '0001148' and MODULE = '0 ' and MCO = '05' and LOC= '00' and INSLINE = 'CA'  )
                 and StateCd=(select top 1 RATESTE  from PR009000 where SYMBOL='CPP' AND POLNUM=0001148 AND MODULE=00 AND LOC=00 AND MCO=05 AND PCO=05 AND UNTRECNUM=1))
            When 'SPLIT' = 'BI' then (select UMLimitTypeDes   from CASeleUMLimitType where UMCoverageTypeCd=( 'SPLIT' )  
                 and UMLimitTypeCd =cast((Select top 1 USAMNT03 From PR006000 Where Symbol= 'CPP' and POLNUM = '0001148' and MODULE = '0 ' and MCO = '05' and LOC= '00' and INSLINE = 'CA' ) AS varchar(100))
                 and StateCd =cast((select top 1 RATESTE  from PR009000 where SYMBOL='CPP' AND POLNUM=0001148 AND MODULE=00 AND LOC=00 AND MCO=05 AND PCO=05 AND UNTRECNUM=1) AS varchar(100)))
            When 'SPLIT' = 'N' or 'SPLIT' = 'SPLIT' then ( select top 1 isnull(UMLimitTypeDes,null) from CASeleUMLimitType where UMCoverageTypeCd=( 'SPLIT' )  
                 and UMLimitTypeCd=(Select top 1 isnull(USAMNT03,null) From PR006000 Where Symbol= 'CPP' and POLNUM = '0001148' and MODULE = '0' and MCO = '05' and LOC= '00' and INSLINE = 'CA' )
                 and StateCd=(select  top 1 isnull(RATESTE,null)  from PR009000 where SYMBOL='CPP' AND POLNUM=0001148 AND MODULE=00 AND LOC=00 AND MCO=05 AND PCO=05 AND UNTRECNUM=1) )
            When 'SPLIT' = 'PD' then (

                select isnull(UMLimitTypeDes,null) from CASeleUMLimitType where UMCoverageTypeCd=( 'SPLIT' )  
                 and UMLimitTypeCd=(Select top 1 isnull(USAMNT04,null) From PR006000 Where Symbol= 'CPP' and POLNUM = '0001148' and MODULE = '0' and MCO = '05' and LOC= '00' and INSLINE = 'CA'  )
                 and StateCd=(select top 1 isnull(RATESTE,null)  from PR009000 where SYMBOL='CPP' AND POLNUM=0001148 AND MODULE=00 AND LOC=00 AND MCO=05 AND PCO=05 AND UNTRECNUM=1))  

        End )
     When isnull('UN',null) then        
        (Case
            When 'SPLIT' = 'CSL' then
                (Select isnull(USAMNT02,null) From PR009000 Where SYMBOL='CPP' AND POLNUM=0001148 AND MODULE=00 AND LOC=00 AND MCO=05 AND PCO=05 AND UNTRECNUM=1 and coverage='UN')
            When 'SPLIT' = 'BI' then
                (Select isnull(USAMNT03,null) From PR009000 Where SYMBOL='CPP' AND POLNUM=0001148 AND MODULE=00 AND LOC=00 AND MCO=05 AND PCO=05 AND UNTRECNUM=1 and INSLINE = 'CA' and coverage='UN')
            When 'SPLIT' = 'N' then
                (Select isnull(USAMNT03,null) From PR009000 Where SYMBOL='CPP' AND POLNUM=0001148 AND MODULE=00 AND LOC=00 AND MCO=05 AND PCO=05 AND UNTRECNUM=1 and INSLINE = 'CA' and coverage='UN')    


        End )

 When 'DOC-MP' then
     (Select USCDE14 From PR006000 where Symbol= 'CPP' and POLNUM = '0001148' and MODULE = '0 ' and MCO = '05' and LOC= '00' and INSLINE = 'CA'  )
 When 'DOC-UM' then
     (Select USIND06 From PR006000 where Symbol= 'CPP' and POLNUM = '0001148' and MODULE = '0 ' and MCO = '05' and LOC= '00' and INSLINE = 'CA'  )
 When 'DOC-UN' then
     (Select USIND05 From PR006000 where Symbol= 'CPP' and POLNUM = '0001148' and MODULE = '0 ' and MCO = '05' and LOC= '00' and INSLINE = 'CA'  )
 When 'DOCUMP' then
     (Select USAMNT04 From PR009000 where SYMBOL='CPP' AND POLNUM=0001148 AND MODULE=00 AND LOC=00 AND MCO=05 AND PCO=05 AND UNTRECNUM=1 and insline = 'CA' and coverage='DOCUMP')
 When 'DOCUNP' then
     (Select USAMNT04 From PR009000 where SYMBOL='CPP' AND POLNUM=0001148 AND MODULE=00 AND LOC=00 AND MCO=05 AND PCO=05 AND UNTRECNUM=1 and insline = 'CA' and coverage='DOCUNP')
 When 'DTHBEN' then
     (Select USAMNT03 From PR009000 where SYMBOL='CPP' AND POLNUM=0001148 AND MODULE=00 AND LOC=00 AND MCO=05 AND PCO=05 AND UNTRECNUM=1 and insline = 'CA' and coverage='DTHBEN')
 When 'HIRE' then
     (Select USAMNT05 From PR006000 where Symbol= 'CPP' and POLNUM = '0001148' and MODULE = '0 ' and MCO = '05' and LOC= '00' and INSLINE = 'CA'  )
 When 'HIREUM' then
     (Select USIND09 From PR006000 where Symbol= 'CPP' and POLNUM = '0001148' and MODULE = '0 ' and MCO = '05' and LOC= '00' and INSLINE = 'CA'  )
 When 'HIREUN' then
     (Select USIND12 From PR006000 where Symbol= 'CPP' and POLNUM = '0001148' and MODULE = '0 ' and MCO = '05' and LOC= '00' and INSLINE = 'CA'  )
     When 'LIAB' then
     (Select USAMNT05 From PR006000 where Symbol= 'CPP' and POLNUM = '0001148' and MODULE = '0 ' and MCO = '05' and LOC= '00' and INSLINE = 'CA'  )
     When 'MEDPAY' then
     (Select USCDE02 From PR006000 where Symbol= 'CPP' and POLNUM = '0001148' and MODULE = '0 ' and MCO = '05' and LOC= '00' and INSLINE = 'CA'  )         
     When 'NONUM' then
     (Select USCDE08 From PR006000 where Symbol= 'CPP' and POLNUM = '0001148' and MODULE = '0 ' and MCO = '05' and LOC= '00' and INSLINE = 'CA'  )         
     When 'SOUND' then
     (Select USAMNT03 From PR009000 where SYMBOL='CPP' AND POLNUM=0001148 AND MODULE=00 AND LOC=00 AND MCO=05 AND PCO=05 AND UNTRECNUM=1 and insline = 'CA' and coverage='SOUND')         
     When 'TRLCMP' then
     (Select USAMNT03 From PR009000 where SYMBOL='CPP' AND POLNUM=0001148 AND MODULE=00 AND LOC=00 AND MCO=05 AND PCO=05 AND UNTRECNUM=1 and insline = 'CA' and coverage='TRLCMP')         
     When 'TRLCOL' then
     (Select USAMNT03 From PR009000 where SYMBOL='CPP' AND POLNUM=0001148 AND MODULE=00 AND LOC=00 AND MCO=05 AND PCO=05 AND UNTRECNUM=1 and insline = 'CA' and coverage='TRLCOL')         
     When 'TRLSP' then
     (Select USAMNT03 From PR009000 where SYMBOL='CPP' AND POLNUM=0001148 AND MODULE=00 AND LOC=00 AND MCO=05 AND PCO=05 AND UNTRECNUM=1 and insline = 'CA' and coverage='TRLSP')         
     When 'UMPD' then
     (Select USAMNT01 From PR009000 where SYMBOL='CPP' AND POLNUM=0001148 AND MODULE=00 AND LOC=00 AND MCO=05 AND PCO=05 AND UNTRECNUM=1 and insline = 'CA' and coverage='UMPD')         
     When 'REG-UM' then
     (Select USAMNT04 From PR009000 where SYMBOL='CPP' AND POLNUM=0001148 AND MODULE=00 AND LOC=00 AND MCO=05 AND PCO=05 AND UNTRECNUM=1 and insline = 'CA' and coverage='REG-UM')     
     When 'REG-UN' then
     (Select USAMNT04 From PR009000 where SYMBOL='CPP' AND POLNUM=0001148 AND MODULE=00 AND LOC=00 AND MCO=05 AND PCO=05 AND UNTRECNUM=1 and insline = 'CA' and coverage='REG-UN')     
     When 'REG-L' then
     (Select USAMNT04 From PR009000 where SYMBOL='CPP' AND POLNUM=0001148 AND MODULE=00 AND LOC=00 AND MCO=05 AND PCO=05 AND UNTRECNUM=1 and insline = 'CA' and coverage='REG-L')     
     When 'REG-MP' then
     (Select USAMNT04 From PR009000 where SYMBOL='CPP' AND POLNUM=0001148 AND MODULE=00 AND LOC=00 AND MCO=05 AND PCO=05 AND UNTRECNUM=1 and insline = 'CA' and coverage='REG-MP')     
     When 'REG-PIP' then
     (Select USAMNT04 From PR009000 where SYMBOL='CPP' AND POLNUM=0001148 AND MODULE=00 AND LOC=00 AND MCO=05 AND PCO=05 AND UNTRECNUM=1 and insline = 'CA' and coverage='REG-PIP')          

    Else
    ( Select '-1' )
    End )
     FROM PR009000 WHERE SYMBOL='CPP' AND POLNUM=0001148 AND MODULE=00 AND LOC=00 AND MCO=05 AND PCO=05 AND UNTRECNUM=1

这里 PR009000 是有这样数据的表。

select *  FROM PR009000 WHERE SYMBOL='CPP' AND POLNUM=0001148 AND MODULE=00 AND LOC=00 AND MCO=05 AND PCO=05 AND UNTRECNUM=1

这是查询中使用的另一个表

Select * From PR006000 Where Symbol= 'CPP' and POLNUM = '0001148' and MODULE = '0 ' and MCO = '05' and LOC= '00' and INSLINE = 'CA'

请让我知道问题出在哪里，解决方案应该是什么。

Answer 1

我们不知道您的表定义，我不会尝试解析您的查询。但问题几乎可以肯定是在概念上，你有类似的东西:

CASE
   WHEN Condition 1 THEN <decimal value>
   WHEN Condition 2 THEN <string value>
END

问题是 CASE expression 的结果必须是单一的、定义良好的数据类型。每个 THEN 子句都必须返回此单一类型的值，或转换为该类型。

当您有多种类型时，SQL Server 将使用各种优先规则选择类型。在本例中，它选择了数字类型，但无法将字符串转换为数字。

解决这个问题的通常方法是:

CASE
   WHEN Condition 1 THEN CONVERT(varchar(200),<decimal value>)
   WHEN Condition 2 THEN <string value>
END

所以现在 SQL Server 会将所有内容都保存为字符串。