从数据类型 varchar 转换为 float 时出错,第一段查询语句没有报错，但是第二段语句出现问题

[ismallgang]

select a.stockid+'-'+e.name as 仓库,b.lb+'-'+d.name as 类别,a.hh as 货号,b.pm_zjm as 助记码,b.pm+(case when isnull(b.bm,'')='' then '' else '/'+b.bm end) as [商品名/通用名],
b.gg as 规格,b.cd as 产地,b.scdw as 生产单位,b.pdw as 单位,a.ph as 批号,convert(varchar(10),(case when a.xq='1900-01-01' then null else a.xq end),23) as 效期,
convert(varchar(10),(case when a.scrq='1900-01-01' then null else a.scrq end),23) as 生产日期,cast(b.zbzl as decimal(18,2)) as 中包装量,cast(b.mjl as decimal(18,2)) as 件数,cast(a.sl as decimal(18,2)) as 数量,a.jhdj as 成本价,
cast(round(a.jhdj*a.sl,2) as decimal(18,2)) as 成本额,cast(b.lsj as decimal(18,2)) as 零售价,cast(round(a.sl*b.lsj,2) as decimal(18,2)) as 零售额,
a.dh as 批次号,a.dwbh as 单位编号,g.mc as 单位名称,convert(varchar(10),a.yhrq,23) as 进货日期
from batchhistory a inner join yw_kck b on a.hh=b.hh left join kclb d on b.lb=d.id left join gl_stock e on a.stockid=e.id left join glsx f on f.fid=1 and b.sx=f.id left join gl_super g on a.dwbh=g.tjbh
where a.fyear='2015' and a.fperiod='1' and a.sl<>0 order by a.stockid,b.lb,a.hh,a.ph

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

select b.lb+'-'+d.name as 类别,sum(cast(round(a.jhdj*a.sl,2) as decimal(18,2))) as 成本额
from kclb d ,yw_kck b,batchhistory a inner join zlhhbm$ k on a.HH=k.货号
where a.fyear='2015' and a.fperiod='1' and a.sl<>0
group by b.LB,d.name,a.jhdj,a.sl order by b.LB



在执行第二段语句的时候报‘从数据类型 varchar 转换为 float 时出错。’a.sl看了下是字符类型，但是第一个语句执行没有问题，请问这个是什么原因，要怎么处理？谢谢。去掉 a.sl<>0 还是报错。

[ismallgang]

select b.lb+'-'+d.name as 类别,sum(cast(round(a.jhdj*a.sl,2) as decimal(18,2))) as 成本额 from kclb d ,yw_kck b,batchhistory a left join zlhhbm$ k on a.HH=k.货号 where a.fyear='2015' and a.fperiod='1' and a.sl<>0 group by b.LB,d.name,a.jhdj,a.sl order by b.LB 这个是不是要把 zlhhbm$ HH列的数据类型给改变一下？

[ismallgang]

求正解

[Ginnnnnnnn]

既然涉及到字符串拼接，就要注意拼接的时候要将拼接的各栏位转成字符串。 比如 a.stockid + '-' + e.name 这里应该是一个 int + 一个字符串 所以报错了。应该尝试将这些变成 rtrim(a.stockid) + '-' + e.name

[ismallgang]

谢谢，已经解决了，导入数据库的临时表数据类型不对应，重现导入了一下就可以了。