| 解决方案： |
请确定是否只使用存货系统还是只使用了存货。
办法2、3是可以使用的。办法四最好别用，根据提供的说明，应该是表定义被修改了,关键字约束错误造成的。
第三种办法改动后不应该有别的问题。一般情况下用第二种办法就可以了。
只使用存货系统可以使用如下语句调整现存量。
Delete From CurrentStock
drop table TempCurrentStock
SELECT cWhCode,cInvCode,CASE WHEN cFree1=‘‘THEN NULL ELSE cFree1 END AS cFree1 ,CASE WHEN cFree2=‘‘THEN NULL ELSE cFree2 END AS cFree2 ,iainquantity AS iQuantity, CAST0 AS FLOAT AS fOutQuantity,CAST0 AS FLOAT AS fInQuantity
INTO TempCurrentStock
From ia_subsidiary
Where cvoutype=‘34‘
group by ia_subsidiary.cWhCode,ia_subsidiary.cInvCode,ia_subsidiary.cFree1,ia_subsidiary.cFree2,ia_subsidiary.iainquantity
INSERT INTO TempCurrentStockcWhCode,cInvCode,cFree1,cFree2,iQuantity SELECT cWhCode, cInvCode,CASE WHEN cFree1=‘‘THEN NULL ELSE cFree1 END AS cFree1 ,CASE WHEN cFree2=‘‘THEN NULL ELSE cFree2 END AS cFree2 , CASE WHEN bRdFlag<>0 THEN iQuantity ELSE -iQuantity END AS iQuantity
FROM RdRecord INNER JOIN RdRecords ON RdRecord.ID = RdRecords.ID
WHERE cVouchType<>‘33‘AND cVouchType<>‘34‘ AND RdRecord.dDate>=‘2001-1-01‘ 该日期为模块启用日期
INSERT INTO TempCurrentStockcWhCode,cInvCode,cFree1,cFree2,fOutQuantity
SELECT EnDispatchs.cWhCode, EnDispatchs.cInvCode,CASE WHEN EnDispatchs.cFree1=‘‘ THEN NULL ELSE EnDispatchs.cFree1 END AS cFree1 ,CASE WHEN EnDispatchs.cFree2=‘‘ THEN NULL ELSE EnDispatchs.cFree2 END AS cFree2 , ISNULLiQuantity,0-ISNULLfOutQuantity,0 AS fOutQuantity
FROM EnDispatch INNER JOIN EnDispatchs ON EnDispatch.EDID = EnDispatchs.EDID WHERE ISNULLEnDispatchs.cWhCode,‘‘<>‘‘ AND ISNULLEnDispatch.cSTCode,‘‘<>‘‘ AND EnDispatchs.iQuantity>0 AND bReturnFlag=0
INSERT INTO TempCurrentStockcWhCode,cInvCode,cFree1,cFree2,fOutQuantity
SELECT DispatchLists.cWhCode as cWhCode, DispatchLists.cInvCode as cInvCode,CASE WHEN DispatchLists.cFree1=‘‘ THEN NULL ELSE DispatchLists.cFree1 END AS cFree1 , CASE WHEN DispatchLists.cFree2=‘‘ THEN NULL ELSE DispatchLists.cFree2 END AS cFree2 , ISNULLiQuantity,0-ISNULLfOutQuantity,0 AS fOutQuantity
FROM DispatchLists INNER JOIN DispatchList ON DispatchLists.DLID = DispatchList.DLID
WHERE DispatchList.cVouchType=‘05‘ AND ISNULLDispatchLists.cWhCode,‘‘<>‘‘ AND ISNULLDispatchList.cSTCode,‘‘<>‘‘ AND DispatchList.iSale=0 or DispatchList.iSale IS NULL AND DispatchLists.iQuantity>0
INSERT INTO TempCurrentStockcWhCode,cInvCode,cFree1,cFree2,fOutQuantity
SELECT cWhCode, cInvCode,CASE WHEN cFree1=‘‘THEN NULL ELSE cFree1 END AS cFree1 , CASE WHEN cFree2=‘‘THEN NULL ELSE cFree2 END AS cFree2 , ISNULLiQuantity,0-ISNULLfOutQuantity,0 AS fOutQuantity
FROM SaleBillVouchs INNER JOIN SaleBillVouch ON SaleBillVouch.SBVID=SaleBillVouchs.SBVID
WHERE ISNULLSaleBillVouchs.cWhCode,‘‘<>‘‘ AND SaleBillVouch.iDisp=0 OR SaleBillVouch.iDisp IS NULL AND ISNULLcBusType,‘‘<>‘委托‘ AND ISNULLcSTCode,‘‘<>‘‘ AND ISNULLcInvalider,‘‘=‘‘ AND SaleBillVouchs.iQuantity>0
INSERT INTO CurrentStockcWhCode,cInvCode,cFree1,cFree2,iQuantity,fOutQuantity,fInQuantity
SELECT cWhCode, cInvCode,cFree1,cFree2 , sumiQuantity AS iQuantity,sumISNULLfOutQuantity,0 AS fOutQuantity, sumISNULLfInQuantity,0 AS fInQuantity
FROM TempCurrentStock
GROUP BY cWhCode,cInvCode,cFree1,cFree2
Delete From CurrentStock
where iquantity is null and foutquantity=0 and finquantity=0 |