SELECT
AssistOutPcsQty=CASE WHEN UPPER(a.Unit)='WP' THEN SUM( ISNULL(a.Qty,0)*b.WplAry*b.AryPcs - ISNULL(a.ScrapPnl,0)*b.AryPcs - ISNULL(a.Scrap,0) - ISNULL(a.ScrapAry,0)*b.AryPcs - ISNULL(a.ScrapPcs,0) )
WHEN UPPER(a.Unit)='PNL' THEN SUM(ISNULL(a.Qty,0)*b.AryPcs - ISNULL(a.Scrap,0) - ISNULL(a.ScrapPcs,0) )
WHEN UPPER(a.Unit)='PCS' THEN SUM(ISNULL(a.Qty,0) - ISNULL(a.Scrap,0) - ISNULL(a.ScrapPcs,0)) End
FROM Ppc_AssistOutBatch a
LEFT JOIN Mkt_PnInfo b ON a.ProdNo=b.ProdNo
WHERE a.DelMark=0
GROUP BY A.UNIT 出现提示:
服务器: 消息 8115,级别 16,状态 2,行 1
Arithmetic overflow error converting expression to data type int.
请问这该怎么解决?
AssistOutPcsQty=CASE WHEN UPPER(a.Unit)='WP' THEN SUM( ISNULL(a.Qty,0)*b.WplAry*b.AryPcs - ISNULL(a.ScrapPnl,0)*b.AryPcs - ISNULL(a.Scrap,0) - ISNULL(a.ScrapAry,0)*b.AryPcs - ISNULL(a.ScrapPcs,0) )
WHEN UPPER(a.Unit)='PNL' THEN SUM(ISNULL(a.Qty,0)*b.AryPcs - ISNULL(a.Scrap,0) - ISNULL(a.ScrapPcs,0) )
WHEN UPPER(a.Unit)='PCS' THEN SUM(ISNULL(a.Qty,0) - ISNULL(a.Scrap,0) - ISNULL(a.ScrapPcs,0)) End
FROM Ppc_AssistOutBatch a
LEFT JOIN Mkt_PnInfo b ON a.ProdNo=b.ProdNo
WHERE a.DelMark=0
GROUP BY A.UNIT 出现提示:
服务器: 消息 8115,级别 16,状态 2,行 1
Arithmetic overflow error converting expression to data type int.
请问这该怎么解决?
已经 A.UNIT 个汇总..那要统计这写数!
--如果是数字可以隐式转换成字符串
declare @table table (mchar varchar(20))
insert into @table
select 'abcd' union all
select 'efgh' union all
select null union all
select 'xyz'
select isnull(mchar,0) from @table--估计问题出在这了,lz可以检查一下a.qty,b.wplary,b.arypcs是不是都是整型的。
isnull(a.qty,0)*b.wplary*b.arypcs