求解：从数据类型 varchar 转换为 bigint 时出错

[李文辉]

select
b.ps as ps,b.id as id,f.org_name as orn,b.user_name as un,f.medical_card as mc,b.baoxiao_date as bd,b.request_money as rm
,b.request_moneya as rma,b.true_money as tm, b.baoxiao_type as bt
,(SELECT TOP 1 sickness_name FROM ws_sickness WHERE sickness_id =b.sick_id) as sk_zy
,b.qt1a as qt,isnull(b.fund_money,'0') jtzh,b.true_money+isnull(b.fund_money,'0') tma
,case b.his_record when '1' then '小医院系统' when '2' then '小医院系统' else '农合系统' end as hr
,mz_true_money mz_tm ,out_date from ws_23_baoxiao_detail b inner join ws_23_family f on b.family_id=f.id
where b.baoxiao_orgid='6' and b.enable_flag IN('1','4') AND b.check_flag='0' and b.baoxiao_type not in (2,7)
and b.baoxiao_date>='2011-06-11'
and b.baoxiao_date<='2012-6-11 23:59:59'


消息 8114，级别 16，状态 5，第 1 行
从数据类型 varchar 转换为 bigint 时出错。

[开着拖拉机泡妞]

你看看你isnull(b.fund_money,'0') jtzh,b.true_money+isnull(b.fund_money,'0') tma
,case b.his_record when '1' then '小医院系统' when '2' then '小医院系统' else '农合系统' end as hr
这些字段里的数据类型对不对应

[Felixzhaowenzhong]

SELECT b.ps                                     AS ps,
       b.id                                     AS id,
       f.org_name                               AS orn,
       b.user_name                              AS un,
       f.medical_card                           AS mc,
       b.baoxiao_date                           AS bd,
       b.request_money                          AS rm,
       b.request_moneya                         AS rma,
       b.true_money                             AS tm,
       b.baoxiao_type                           AS bt,
       (SELECT TOP 1 sickness_name
        FROM   ws_sickness
        WHERE  sickness_id = b.sick_id)         AS sk_zy,
       b.qt1a                                   AS qt,
       Isnull(b.fund_money, '0')                jtzh,
       b.true_money + Isnull(b.fund_money, '0') tma,
       CASE b.his_record
         WHEN '1' THEN '小医院系统'
         WHEN '2' THEN '小医院系统'
         ELSE '农合系统'
       END                                      AS hr,
       mz_true_money                            mz_tm,
       out_date
FROM   ws_23_baoxiao_detail b
       INNER JOIN ws_23_family f
               ON b.family_id = f.id
WHERE  b.baoxiao_orgid = '6'
       AND b.enable_flag IN( '1', '4' )
       AND b.check_flag = '0'
       AND b.baoxiao_type NOT IN ( 2, 7 )
       AND b.baoxiao_date >= '2011-06-11'
       AND b.baoxiao_date <= '2012-6-11 23:59:59' 


--应该是你 这里的问题
 CASE b.his_record
         WHEN '1' THEN '小医院系统'
         WHEN '2' THEN '小医院系统'
         ELSE '农合系统'
       END                                      AS hr,

 

[Felixzhaowenzhong]

这里 Isnull(b.fund_money, '0') jtzh,
b.true_money + Isnull(b.fund_money, '0') tma,

改为
Isnull(b.fund_money, 0) jtzh,
b.true_money + Isnull(b.fund_money, 0) tma,

[孤独加百列]

select	b.ps as ps,
		b.id as id,
		f.org_name as orn,
		b.user_name as un,
		f.medical_card as mc,
		b.baoxiao_date as bd,
		b.request_money as rm,
		b.request_moneya as rma,
		b.true_money as tm, 
		b.baoxiao_type as bt,
		(SELECT TOP 1 sickness_name FROM ws_sickness WHERE sickness_id =b.sick_id) as sk_zy,
		b.qt1a as qt,
		isnull(b.fund_money,0) jtzh,
		b.true_money+isnull(b.fund_money,0) tma,
		case b.his_record when '1' then '小医院系统' 
						  when '2' then '小医院系统' 
						  else '农合系统' end as hr,
		mz_true_money mz_tm ,
		out_date 
from	ws_23_baoxiao_detail b inner join ws_23_family f on b.family_id=f.id  
where	b.baoxiao_orgid='6' 
		and b.enable_flag IN('1','4') 
		AND b.check_flag='0' 
		and b.baoxiao_type not in (2,7)  
		and b.baoxiao_date>='2011-06-11' 
		and b.baoxiao_date<='2012-6-11 23:59:59'

类型是整形的就直接写数字，不要加单引号。

[發糞塗牆]

某些数据类型支持隐式转换，所以楼主使用的时候要注意，为了避免，尽量使用规范化编程。该写的一定要写，可写可不写的也一定要写。