查询存货明细账数据错误方俊霞2010年10月【问题描述】只有存货核算模块，且是6月份启用的软件，现在查询存货明细账。若过滤条件不选择日期范围，则6月底某存货的结存数量与结存金额都为0；若过滤条件选择日期范围为2010-07-01——现在，则期初有结存数量和金额。【问题现象展现】1、如下图：查询01仓库01071存货，日期范围不选1、如下图：查询01仓库01071存货，日期范围不选显示6月底的结存数量和结存金额都为0显示6月底的结存数量和结存金额都为02、重新查询该仓库该存货的明细账，日期范围选择为：2010-07-01——现在2、重新查询该仓库该存货的明细账，日期范围选择为：2010-07-01——现在显示如下：期初结存有结存数量和结存金额显示如下：期初结存有结存数量和结存金额【分析处理过程】1、存货明细帐的数据基本上都是取自表ia_subsidiary的。2、针对客户现在的问题是出现在日期范围选择的不同，最终的结存数据不同。故：带着疑惑，查询后台数据表，看看结存数据是否正确吧！因为客户只有存货核算模块，故查询后台，参见语句如下：我们可以看出正常情况下结存数量=10-7=3即等于选择日期范围2010-07-01——现在，前台查询存货明细账的结果。我们可以看出正常情况下结存数量=10-7=3即等于选择日期范围2010-07-01——现在，前台查询存货明细账的结果。3、因为ia_subsidiary表本是不涉及结存数量/金额字段的，其结存数量和金额都是按照记账的后的收发记录汇总得到的。故再看下后台的明细记录吧！从上图我们可以看出该存货当前年度一共发生了3笔记录。再回顾看下前台的存货明细帐表发现前台一共发生4笔记录，且有单据号重复的记录。发现前台一共发生4笔记录，且有单据号重复的记录。联查单据，发现单据上实际就一行记录Rdrecord/s、Ia_subsidiary也都正常，没有重复记录呀！Rdrecord/s、Ia_subsidiary也都正常，没有重复记录呀！那为什么后台正确，前台反而查询不正确呢？那为什么后台正确，前台反而查询不正确呢？【思考】后台的ia_subsidiary以及rdrecord/s都正常，反而是软件前台查询有问题，分析一定是软件在关联查询的时候出现重复记录所致。跟踪看看吧！按照检索出来的语句，在sql中执行如上语句，发现关联重复记录，如下图：按照检索出来的语句，在sql中执行如上语句，发现关联重复记录，如下图：参见语句如下：SELECTIA_Subsidiary.dKeepDate,month(dkeepdate)AsiMonth,day(dkeepdate)AsiDay,A.csign+''+Cast(A.ino_idAsnvarchar(10))AsiPzNo,IA_Subsidiary.cInvHead,IA_Subsidiary.cDifHead,IA_Subsidiary.cWhCode,IA_Subsidiary.cInvCode,IA_Subsidiary.cVouCode,IA_Subsidiary.dVouDate,Warehouse.cWhName,IA_Subsidiary.cBusCode,IA_Subsidiary.cPZdigest,Rd_Style.cRdName,isnull(Vendor.cVenName,Vendor.cVenabbName)cVenName,isnull(customer.ccusname,customer.ccusabbname)asccusname,IA_Subsidiary.cItem_class,IA_Subsidiary.cItemCode,IA_Subsidiary.cAccounter,strContractCode,cproordercode,iproorderids,cworkprocode,cworkcentername,csaleordercode,isaleorderids,IA_Subsidiary.exoCode,IA_Subsidiary.iExRowno,centrustordercode,cpurordercode,cIMOrdercode,IA_Subsidiary.cDefine1,IA_Subsidiary.cDefine2,IA_Subsidiary.cDefine3,IA_Subsidiary.cDefine4,IA_Subsidiary.cDefine5,IA_Subsidiary.cDefine6,IA_Subsidiary.cDefine7,IA_Subsidiary.cDefine8,IA_Subsidiary.cDefine9,IA_Subsidiary.cDefine10,IA_Subsidiary.cDefine11,IA_Subsidiary.cDefine12,IA_Subsidiary.cDefine13,IA_Subsidiary.cDefine14,IA_Subsidiary.cDefine15,IA_Subsidiary.cDefine16,IA_Subsidiary.cDefine22,IA_Subsidiary.cDefine23,IA_Subsidiary.cDefine24,IA_Subsidiary.cDefine25,IA_Subsidiary.cDefine26,IA_Subsidiary.cDefine27,IA_Subsidiary.cDefine28,IA_Subsidiary.cDefine29,IA_Subsidiary.cDefine30,IA_Subsidiary.cDefine31,IA_Subsidiary.cDefine32,IA_Subsidiary.cDefine33,IA_Subsidiary.cDefine34,IA_Subsidiary.cDefine35,IA_Subsidiary.cDefine36,IA_Subsidiary.cDefine37,IA_Subsidiary.cFree1,IA_Subsidiary.cFree2,IA_Subsidiary.cFree3,IA_Subsidiary.cFree4,IA_Subsidiary.cFree5,IA_Subsidiary.cFree6,IA_Subsidiary.cFree7,IA_Subsidiary.cFree8,IA_Subsidiary.cFree9,IA_Subsidiary.cFree10,Inventory.cInvDefine1,Inventory.cInvDefine2,Inventory.cInvDefine3,Inventory.cInvDefine4,Inventory.cInvDefine5,Inventory.cInvDefine6,Inventory.cInvDefine7,Inventory.cInvDefine8,Inventory.cInvDefine9,Inventory.cInvDefine10,Inventory.cInvDefine11,Inventory.cInvDefine12,Inventory.cInvDefine13,Inventory.cInvDefine14,Inventory.cInvDefine15,Inventory.cInvDefine16,ltrim(Cast(caseIA_Subsidiary.bRdFlagwhen1thenIA_Subsidiary.iInCostelseIA_Subsidiary.iOutcostendasdecimal(20,6))),ltrim(Cast(ltrim(Cast(IA_Subsidiary.iAInQuantityasdecimal(20,6)))/cast(caseInventory.iGroupTypewhen1thenCU_F.iChangRateelseNULLendasdecimal(34,8))asdecimal(20,6)))asiAInQPiece,ltrim(Cast(IA_Subsidiary.iAInQuantityasdecimal(20,6))),ltrim(Cast(IA_Subsidiary.iInCostasdecimal(20,6))),ltrim(Cast(IA_Subsidiary.iAInPriceasdecimal(20,2))),ltrim(Cast(IA_Subsidiary.iAOutQuantity/cast(caseInventory.iGroupTypewhen1thenCU_F.iChangRateelseNULLendasdecimal(34,8))asdecimal(20,6)))asiAOutQPiece,ltrim(Cast(IA_Subsidiary.iAOutQuantityasdecimal(20,6))),ltrim(Cast(IA_Subsidiary.iOutCostasdecimal(20,6))),ltrim(Cast(IA_Subsidiary.iAOutPriceasdecimal(20,2))),'','','','',convert(varchar(30),IA_Subsidiary.iDebitDifCost),convert(varchar(30),IA_Subsidiary.iCreditDifCost),'',IA_Subsidiary.cVouType,IA_Subsidiary.AutoID,IA_Subsidiary.ID,IA_Subsidiary.bRdFlag,IA_Subsidiary.cPZID,IA_Subsidiary.iPZIDFROMIA_Subsidiarywith(nolock)LEFTJOINVendorwith(nolock)ONVendor.cVenCode=IA_Subsidiary.cVenCodeLEFTJOINRd_Stylewith(nolock)ONRd_Style.cRdCode=IA_Subsidiary.cRdCodeLEFTJOINInventorywith(nolock)ONInventory.cInvCode=IA_Subsidiary.cInvCodeleftjoinComputationUnitCU_Fwith(nolock)ONCU_F.cGroupCode=Inventory.cGroupCodeANDCU_F.bMainUnit=0andCU_F.iNumber=0LeftJoinWarehousewith(nolock)OnIA_Subsidiary.cWhCode=Warehouse.cWhCodeLeftJoinCustomerwith(nolock)ONIA_Subsidiary.cCusCode=customer.ccuscodeleftJOin(selectino_id,csign,coutno_idfromgl_accvouchwith(nolock)wherecOutsysName='IA'and(iflagisnulloriflag=2)GroupByino_id,csign,coutno_id)AONIA_Subsidiary.cPzID=A.coutno_idWhereIA_Subsidiary.dKeepDate<='2010-12-31'ANDia_subsidiary.imonth<>0ANDIA_Subsidiary.cWhCode=N'01'AND(NOTIA_Subsidiary.cWhCodeisnull)AND((IA_Subsidiary.cWhCodeIN(SelectcWhCodeFromWarehouseWherebInCost=1))orIA_Subsidiary.cWhCodeisnull)andia_subsidiary.cinvcode=N'01071'ANDIA_Subsidiary.iMonth<=12ANDIA_Subsidiary.cVouType<>N'33'ANDIA_Subsidiary.cVouType<>N'34'ORDERBYIA_Subsidiary.dKeepDate,isnull(IA_Subsidiary.bakID,IA_Subsidiary.AutoID),IA_Subsidiary.AutoID分析如上语句，推测问题是出在gl_accvouch了！！有了问题思路，赶紧再查询下看看吧！对比明细账的金额和凭证金额，判断是单据号0000000039在gl_accvouch.coutno_id不正确所致。好在客户的数据比较少，单据号为0000000039的单据也就一张，故：按照正确的值替换后问题解决。【总结】此类问题，若混乱的数据多，则无好方法处理，只能清空gl_accvouch.coutno_id。引起的原因多数是导入的凭证与本帐套的凭证线索号冲突所致！
