猿问

以下是从数据类型 varchar 转换为 numeric 时出错”,不知道错在哪了,求助

需要用sql server 数据库:如果某种中药的库存数量为1000,当日出库数量为100,需要计算可用天数即1000/100=10,并根据这个天数正序排列,当库存数量为0或者当日出库数量为0,则在天数处显示没有库存或者当日未出库。可是当我用case判断在库存数量与当日出库数量都不为0的时候做除法运算的时候出现“从数据类型 varchar 转换为 numeric 时出错”。不知道错在哪了,求大神指教。代码如下:(注:a.num也就是kcyp.[库存数量]的数据类型是numeric(18, 5),b.cknum也就是db_data.[数量]的数据类型也是numeric(18, 5))

select a.*,b.*
,
case when a.num=0 then '库存为0,不能做除法'
when b.cknum IS null then '当天无取出,无法计算天数'
when a.num<>0 and b.cknum<>0 then cast(a.num as numeric(18, 5))/cast(b.cknum as numeric(18, 5))end
as tianshu

from
(SELECT ypwc.[名称] as y_name,ypwc.[编号] as bianhao,ypwc.[药品类型] as ytype,ypwc.[规格] as guige,ypwc.[药品单位] as danwei,kcyp.[仓库编号] as cangku,kcyp.[库存数量] as num FROM [T库存药品] as kcyp right join [T药品卫材] as ypwc on kcyp.[药品编号]=ypwc.[编号] where kcyp.[仓库编号]='7' and ypwc.[药品类型]='中药') as a
left join
(select sum(db_data.[数量]) as cknum,db_data.[编号] as bianhaob from [D调拨单] as db_dan inner join [D调拨数据] as db_data on db_dan.[单据编号]=db_data.[单据编号] where db_dan.[过单日期]>='2014-03-05 00:00:00' and db_dan.[过单日期]<='2014-03-05 23:59:59' group by db_data.[编号]) as b
on
a.bianhao = b.bianhaob

order by tianshu asc

守着星空守着你
浏览 279回答 2
2回答

jeck猫

cast(a.num as numeric(18, 5))/cast(b.cknum as numeric(18, 5) 这个得到的是数值类型,而你前面两个得到的是字符类型,我认为只要把这个直接转字符就可以了:to_char(a.num/b.cknum, $99999.99)

DIEA

一个小建议,转换前用ISNUMERIC()函数试一下,就可以保证不出错了,CASE WHEN ISNUMERIC(123) = 1 THEN CAST(.. AS INT) ELSE NULL END
随时随地看视频慕课网APP
我要回答