转型问题.....急,,,

Question

select cbsj,yhmc,yhdz,syds,byds,byyql,lsqf,ysje,isnull(CONVERT(NUMERIC(18, 2),lsqf),0.00)+isnull(CONVERT(NUMERIC(18, 2),ysje),0.00)) AS YKJE from T_YHDAB A,T_LSCBJLB B WHERE A.DAH=B.DAH AND B.CBYF='200908'里面的字段都是字符型提示:将数据类型 varchar 转换为 numeric 时出错。

Answer 1

用 isnumeric(字段名) 判断一下

Answer 2

ysje这个字段中存在有非数值类型的值

Answer 3

select cbsj,yhmc,yhdz,syds,byds,byyql,lsqf,ysje,isnull(CONVERT(NUMERIC(18,2),lsqf),0.00)+isnull(CONVERT(NUMERIC(18, 2),ysje),0.00)) AS YKJE
from T_YHDAB A,T_LSCBJLB B 
WHERE A.DAH=B.DAH AND B.CBYF='200908'
    and isnumeric(lsqf)=1 and isnumeric(ysje)=1

Answer 4

select cbsj,yhmc,yhdz,syds,byds,byyql,lsqf,ysje,
isnull(case when isnumeric(lsqf)=1 then CONVERT(NUMERIC(18,2),lsqf) else 0 end,0.00)+
isnull(case when isnumeric(ysje)=1 then CONVERT(NUMERIC(18, 2),ysje) else 0 end,0.00)) AS YKJE 
from T_YHDAB A,T_LSCBJLB B 
WHERE A.DAH=B.DAH AND B.CBYF='200908' 还是要这样?

Answer 5

 建议先看看是不是有非数值型数据
select * from tb where isnumbric(ysje)!=1 or isnumeric(lsqf)!=1