Question

从数据类型 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

Answer 1

一、有null值；

二、有“”值；

ISNUMERIC确定表达式是否是有效的数字类型。以下值在SQL函数ISNUMERIC()中没有找到:

1.有“-”值；

2.财务上用的逗号，有“，”值

把以上转为0，就可以了。

其他：转小数值一般不要超过5位

扩展资料：

注意事项

sql的函数有个isNumeric(参数) 用来转换数字，如果成功反回1，不成功，则反回0。

例： select case when isNumeric(字段)=0 then 0 else 字段运算 end from 表名  

declare @参数 varchar(32)

set @参数='abc'

select  case when isNumeric(@参数)=0 then 0 else @参数 end 

set @参数='123'

select  case when isNumeric(@参数)=0 then 0 else @参数 end

VARCHAR是一种比CHAR更灵活的数据类型，也用于表示字符数据，但是VARCHAR可以保存可变长度的字符串。其中M表示数据类型中允许保存的字符串的最大长度，只要长度小于数据类型中可以保存的最大长度。

因此，对于难以估计确切长度的数据对象，使用VARCHAR数据类型更为明智。VARCHAR数据类型支持的最大长度也是255个字符。

Answer 2

一、有null值；

二、有“”值；

ISNUMERIC确定表达式是否为一个有效的数字类型。下面这几个值，用sql函数ISNUMERIC()用查不出来错误：

1、有“-”值；

2、财务上用的逗号，有“，”值

把以上转为0，就可以了。

其他：转小数值一般不要超过5位

扩展资料：

注意事项

sql的函数有个isNumeric(参数) 用来转换数字，如果成功反回1，不成功，则反回0。

例： select case when isNumeric(字段)=0 then 0 else 字段运算 end from 表名  

declare @参数 varchar(32)

set @参数='abc'

select  case when isNumeric(@参数)=0 then 0 else @参数 end 

set @参数='123'

select  case when isNumeric(@参数)=0 then 0 else @参数 end

Answer 3

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

追问

我试了，出现了错误：'to_char' 不是可以识别的 内置函数名称。（我是在sql_server里运行的，你写的是什么数据库的内置函数？）还有就是$99999.99
是什么？

追答

哦，抱歉，我写的是oracle中的用法，
sqlserver 这样写应该行：
cast(a.num/b.cknum as varchar(2))

追问

自己弄好了，不过还是谢谢你，你的这个我刚刚也试了，还是有错误：将 numeric 转换为数据类型 varchar 时出现算术溢出错误。

追答

那应该是除法结果太长 $99999.99是规定格式，即5位整数，2位小数

Answer 4

一个小建议，转换前用ISNUMERIC()函数试一下，就可以保证不出错了，
CASE WHEN ISNUMERIC(123) = 1 THEN CAST(.. AS INT) ELSE NULL END