SqlServer Conversion failed when converting the nvarchar value ‘.‘ to data type int.

一.报错信息:

Conversion failed when converting the nvarchar value ‘.’ to data type int.

翻译:转换nvarchar值“”时转换失败到数据类型int。

二.报错原因:

1.字段取数确实有问题,无法进行强制转换。
2.数据库算力不足,直接报错。
(我们下面细说2的解决方案)

三.解决方案:

解决方案一:
小部分数据(我碰到的是30w数据,字段截取后得到数字进行计算),算力不足:

添加自定义字段,Row_Number() Over(Order by 字段) FROWID
案例如下:

select 
ROW_NUMBER() Over( Order by c1.FNUMBER) FRowID,
c1.FNUMBER
,(case when c.F_add_Text222 ='' then 1 else (convert(int,(substring(c.F_add_Text,0,CHARINDEX('*',c.F_add_Text))))*convert(int,( substring(c.F_add_Text,CHARINDEX('*',c.F_add_Text)+1,len(c.F_add_Text ))))*0.000001)/( convert(int,(substring(c.F_add_Text222,0,CHARINDEX('*',c.F_add_Text222))))*convert(int,( substring(c.F_add_Text222,CHARINDEX('*',c.F_add_Text222)+1,len(c.F_add_Text222 ))))*0.000001) end) * FREALQTY FREALQTY
from T_PRD_INSTOCK a
left join T_PRD_INSTOCKENTRY b on a.FID = b.FID
left join T_BD_MATERIAL c on b.FMATERIALID = c.FMATERIALID
left join T_ENG_BOM d on c.FMATERIALID = d.FMATERIALID
left join T_ENG_BOMCHILD e on d.FID = e.FID
left join T_BD_MATERIAL c1 on e.FMATERIALID = c1.FMATERIALID
where a.FDOCUMENTSTATUS = 'C' and d.FDOCUMENTSTATUS = 'C'
and a.FDATE >= dateadd(month,-6,Convert(varchar(7),'2023-03-23',120)+ '-01') 
and a.FDATE < Convert(varchar(7),'2023-03-23',120)+ '-01'

最终结果:成功得到数据。

解决方案二:
大部分数据计算或者算数公式过于复杂,算力不足:

添加中间临时表,分担计算压力:

案例如下:

1.临时表存在则删除

if exists (select 1 from sys.objects where name = 'theNowTable') begin drop table theNowTable end

2.创建临时表数据

select * into theNowTable from (select 
ROW_NUMBER() Over( Order by c1.FNUMBER) RowID,c1.FNUMBER
,(case when c.F_add_Text222 ='' then 1 else (convert(int,(substring(c.F_add_Text,0,CHARINDEX('*',c.F_add_Text))))*convert(int,( substring(c.F_add_Text,CHARINDEX('*',c.F_add_Text)+1,len(c.F_add_Text ))))*0.000001)/( convert(int,(substring(c.F_add_Text222,0,CHARINDEX('*',c.F_add_Text222))))*convert(int,( substring(c.F_add_Text222,CHARINDEX('*',c.F_add_Text222)+1,len(c.F_add_Text222 ))))*0.000001) end) * FREALQTY FREALQTY
from T_PRD_INSTOCK a
left join T_PRD_INSTOCKENTRY b on a.FID = b.FID
left join T_BD_MATERIAL c on b.FMATERIALID = c.FMATERIALID
left join T_ENG_BOM d on c.FMATERIALID = d.FMATERIALID
left join T_ENG_BOMCHILD e on d.FID = e.FID
left join T_BD_MATERIAL c1 on e.FMATERIALID = c1.FMATERIALID
where a.FDOCUMENTSTATUS = 'C' and d.FDOCUMENTSTATUS = 'C' and c1.FForbidStatus = 'A' and c.FForbidStatus = 'A'
and a.FDATE >= dateadd(month,-6,Convert(varchar(7),'2023-03-23',120)+ '-01') 
and a.FDATE < Convert(varchar(7),'2023-03-23',120)+ '-01'
union all
select	--简单生产入库单
ROW_NUMBER() Over( Order by c1.FNUMBER) RowID,c1.FNUMBER
,(case when c.F_add_Text222 ='' then 1 else (convert(int,(substring(c.F_add_Text,0,CHARINDEX('*',c.F_add_Text))))*convert(int,( substring(c.F_add_Text,CHARINDEX('*',c.F_add_Text)+1,len(c.F_add_Text ))))*0.000001)/( convert(int,(substring(c.F_add_Text222,0,CHARINDEX('*',c.F_add_Text222))))*convert(int,( substring(c.F_add_Text222,CHARINDEX('*',c.F_add_Text222)+1,len(c.F_add_Text222 ))))*0.000001) end) * FREALQTY FREALQTY
from T_SP_INSTOCK a
left join T_SP_INSTOCKENTRY b on a.FID = b.FID
left join T_BD_MATERIAL c on b.FMATERIALID = c.FMATERIALID
left join T_ENG_BOM d on c.FMATERIALID = d.FMATERIALID
left join T_ENG_BOMCHILD e on d.FID = e.FID
left join T_BD_MATERIAL c1 on e.FMATERIALID = c1.FMATERIALID
where a.FDOCUMENTSTATUS = 'C' and d.FDOCUMENTSTATUS = 'C' and c1.FForbidStatus = 'A'  and c.FForbidStatus = 'A'
and a.FDATE >= dateadd(month,-6,Convert(varchar(7),'2023-03-23',120)+ '-01') 
and a.FDATE < Convert(varchar(7),'2023-03-23',120)+ '-01'
union all
select	--生产退库单
ROW_NUMBER() Over( Order by c1.FNUMBER) RowID,c1.FNUMBER
,(case when c.F_add_Text222 ='' then 1 else (convert(int,(substring(c.F_add_Text,0,CHARINDEX('*',c.F_add_Text))))*convert(int,( substring(c.F_add_Text,CHARINDEX('*',c.F_add_Text)+1,len(c.F_add_Text ))))*0.000001)/( convert(int,(substring(c.F_add_Text222,0,CHARINDEX('*',c.F_add_Text222))))*convert(int,( substring(c.F_add_Text222,CHARINDEX('*',c.F_add_Text222)+1,len(c.F_add_Text222 ))))*0.000001) end) * FREALQTY * -1  FREALQTY
from T_PRD_RESTOCK a
left join T_PRD_RESTOCKENTRY b on a.FID = b.FID
left join T_BD_MATERIAL c on b.FMATERIALID = c.FMATERIALID
left join T_ENG_BOM d on c.FMATERIALID = d.FMATERIALID
left join T_ENG_BOMCHILD e on d.FID = e.FID
left join T_BD_MATERIAL c1 on e.FMATERIALID = c1.FMATERIALID
where a.FDOCUMENTSTATUS = 'C' and d.FDOCUMENTSTATUS = 'C' and c1.FForbidStatus = 'A'  and c.FForbidStatus = 'A'
and a.FDATE >= dateadd(month,-6,Convert(varchar(7),'2023-03-23',120)+ '-01') 
and a.FDATE < Convert(varchar(7),'2023-03-23',120)+ '-01'
union all
select		--简单生产退库单
ROW_NUMBER() Over( Order by c1.FNUMBER) RowID,c1.FNUMBER
,(case when c.F_add_Text222 ='' then 1 else (convert(int,(substring(c.F_add_Text,0,CHARINDEX('*',c.F_add_Text))))*convert(int,( substring(c.F_add_Text,CHARINDEX('*',c.F_add_Text)+1,len(c.F_add_Text ))))*0.000001)/( convert(int,(substring(c.F_add_Text222,0,CHARINDEX('*',c.F_add_Text222))))*convert(int,( substring(c.F_add_Text222,CHARINDEX('*',c.F_add_Text222)+1,len(c.F_add_Text222 ))))*0.000001) end) * FOUTQTY * -1  FOUTQTY
from T_SP_OUTSTOCK a
left join T_SP_OUTSTOCKENTRY b on a.FID = b.FID 
left join T_BD_MATERIAL c on b.FMATERIALID = c.FMATERIALID
left join T_ENG_BOM d on c.FMATERIALID = d.FMATERIALID
left join T_ENG_BOMCHILD e on d.FID = e.FID
left join T_BD_MATERIAL c1 on e.FMATERIALID = c1.FMATERIALID
where a.FDOCUMENTSTATUS = 'C' and d.FDOCUMENTSTATUS = 'C' and c1.FForbidStatus = 'A'  and c.FForbidStatus = 'A'
and a.FDATE >= dateadd(month,-6,Convert(varchar(7),'2023-03-23',120)+ '-01') 
and a.FDATE < Convert(varchar(7),'2023-03-23',120)+ '-01') tab

3.用临时表计算获得最终数据:

select FNUMBER,sum(FREALQTY) FREALQTY from  theNowTable group by FNUMBER 
having sum(FREALQTY) >= 4200

最终结果:成功得到数据。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值