物料审核报错：“不能在具有唯一索引'IDX_HS_INIVSTOCKDIMENSION'的对象 中插入重复键的行。原创
羊羊羊羊羊羊
78人赞赏了该文章
1357次浏览
编辑于2024年01月30日 14:06:50
物料审核报错：“不能在具有唯一索引 'IDX_HS_INIVSTOCKDIMENSION' 的对象 'dbo.T_HS_INIVSTOCKDIMENSION' 中插入重复键的行。语句已终止。”
---修量修复反审核重新审核报错库存维度重复
--查询物料组织，执行以下修复脚本后这里查询出来的核算组织要重新核算
select d.fnumber 核算组织编码, c.fnumber 物料编码,a.FMATERIALID,a.FCARGOOWNERTYPE,a.FLOT,a.FCARGOOWNERID,
a.FSTOCKID,a.FSTOCKSTATUSID,a.FAUXPROPID,a.FSTOCKID,a.FSTOCKLOCID,a.FBOMID,
a.FMTONO,b.FACCTGRANGEID,count(1) from T_HS_INIVSTOCKDIMENSION a
inner join T_HS_STOCKDIMENSION b on a.FDIMEENTRYID=b.FENTRYID
join T_BD_MATERIAL c on a.fmaterialid=c.fmaterialid
join T_ORG_ORGANIZATIONS d on FCARGOOWNERID=d.forgid
group by d.fnumber , c.fnumber, a.FMATERIALID,a.FCARGOOWNERTYPE,a.FCARGOOWNERID,a.FSTOCKID,a.FSTOCKSTATUSID,a.FAUXPROPID,a.FSTOCKID,a.FSTOCKLOCID,a.FBOMID,a.FMTONO,b.FACCTGRANGEID,a.FLOT
having count (1)>1
---1、备份
select * into T_HS_INIVSTOCKDIMENSION_sqlbak_20230831 from T_HS_INIVSTOCKDIMENSION
select * into T_HS_STOCKDIMENSION_sql_bak_20230831 from T_HS_STOCKDIMENSION
---2、创建中间表
select Max(a.FENTRYID) as FENTRYID ,Max(a.fdimeentryid) fdimeentryid into tmpdim
from T_HS_INIVSTOCKDIMENSION a
inner join T_HS_STOCKDIMENSION b on a.FDIMEENTRYID=b.FENTRYID
join T_BD_MATERIAL c on a.fmaterialid=c.fmaterialid
group by c.fnumber, a.FMATERIALID,a.FCARGOOWNERTYPE,a.FCARGOOWNERID,a.FSTOCKID,a.FSTOCKSTATUSID,a.FAUXPROPID,a.FSTOCKID,a.FSTOCKLOCID,a.FBOMID,a.FMTONO,b.FACCTGRANGEID,a.FLOT
having count (1)>1
--3、删除重复数据
delete from T_HS_INIVSTOCKDIMENSION where FENTRYID not in (
select FDIMEENTRYID from T_HS_INIVBALANCE_H
union all
select FDIMEENTRYID from T_HS_INIVBALANCE where FENDINITKEY='0'
union all
select FDIMEENTRYID from T_HS_OUTINSTOCKSEQ_H )
and FENTRYID in (select FENTRYID from tmpdim )
delete from T_HS_STOCKDIMENSION where FENTRYID not in (
select FACCTGDIMEENTRYID from T_HS_INIVBALANCE_H
union all
select FACCTGDIMEENTRYID from T_HS_INIVBALANCE where FENDINITKEY='0'
union all
select FACCTGDIMEENTRYID from T_HS_OUTINSTOCKSEQ_H
union all
select FDIMEENTRYID from T_HS_BALANCE_H
union all
select FDIMEENTRYID from T_HS_BALANCE where FENDINITKEY='0'
union all
select FDIMEENTRYID from T_HS_INIVSTOCKDIMENSION
) and FENTRYID in (select fdimeentryid from tmpdim )
---4、删除临时表
drop table tmpdim
推荐阅读