U8 10.1发货单列表只看见条数，看不见明细

jojo_bj

问题描述：U8 10.1，2012-2013年销售发货单列表只显示条数不显示记录明细，2014年正常。
一阵窃喜，因为有老师曾经教过我这个问题的处理方法(此方法可解决大部分看不到单据列表明细数据的问题)：
DELETE FROM AA_ColumnDicCache_Main WHERE cKey='01'
DELETE FROM AA_ColumnDicCache_Detail WHERE cKey='01'
--发货单列表的ckey是01，销售订单好像是17

执行完SQL重新登录，尼玛啊，怎么还这样。

看来要重新开始了，进入正题(有sql基础且感兴趣的可看懂，不感兴趣勿购买主体，不想骗你金币)


经查看数据库，2012-2013年为一个库，2014年为一个库
打开事件探查器运行跟踪

为了清晰看到我想看的内容我只勾选了这些。(不懂怎么打开的自己百度)
打开发货单列表前清空跟踪信息
在查看跟踪到的语句发现如下觉得有价值的语句

1. 
   
2. <font color="#ff00ff">select cextendfield,cextendjoin from voucherextendinfo where cardnumber='01' and cextendtype='L'</font>
   
3. go
   
4. if exists (select 1 From tempdb..sysobjects  where  name like '#divTable3ED2FB6CA322401588E11889AFFF7283%'  and   type = 'U') drop table #divTable3ED2FB6CA322401588E11889AFFF7283
   
5. select IDENTITY(int, 1,1) as divID,Sales_FHD_W.idlsid as tmpPrimaryID into #divTable3ED2FB6CA322401588E11889AFFF7283 from Sales_FHD_T inner join Sales_FHD_W on Sales_FHD_T.dlid=Sales_FHD_W.dlid  left join (select cInvAddCode as extend_b_cinvcode_v_inventory_cinvaddcode,cPosition as extend_b_cinvcode_v_inventory_cposition,cinvcode as keyextend_b_cinvcode_v_inventory_cinvcode from v_inventory) extend_b_cinvcode_v_inventory on keyextend_b_cinvcode_v_inventory_cinvcode=Sales_FHD_W.cinvcode left join (select cCusName as extend_t_ccuscode_customer_ccusname,cCCCode as extend_t_ccuscode_customer_ccccode,cDCCode as extend_t_ccuscode_customer_cdccode,iCusCreLine as extend_t_ccuscode_customer_icuscreline,ccuscode as keyextend_t_ccuscode_customer_ccuscode from customer) extend_t_ccuscode_customer on keyextend_t_ccuscode_customer_ccuscode=Sales_FHD_T.ccuscode where  1=1   And ((dDate >= N'2013-01-01') And (dDate <= N'2013-01-01'))   order by cdlcode ASC ;
   
6. CREATE INDEX tmpprimaryid_idx ON #divTable3ED2FB6CA322401588E11889AFFF7283( tmpprimaryid)
   
7. go
   
8. <font color="#ff0000">select * from select</font> '' as selcol,irowno,cdlcode,ddate,(case when bfirst=1 then '是' else '否' end) as bfirst,isale,(case when breturnflag=1 then '是' else '否' end) as breturnflag,itb,csettleall,cvouchtype,cbustype,cstcode,cstname,ccuscode,ccusabbname,ccusname,cdepcode,cdepname,cpersoncode,cpersonname,cexch_name,iexchrate,ccontractid,ccontracttagcode,(Sales_FHD_W.cSoCode) as bodysocode,(cordercode) as cordercode,(iorderrowno) as iorderrowno,ccorcode,csbvcode,cwhcode,cwhname,cinvcode,cinvaddcode,cinvname,ccusinvcode,ccusinvname,cinvstd,cvenabbname,iinvweight,fretqtywkp,fretqtyykp,cbatch,dmdate,imassdate,cmassunit,dvdate,ccode,(cvmivencode) as cvmivencode,(cvmivenname) as cvmivenname,(iexpiratdatecalcu) as iexpiratdatecalcu,(cbatchproperty1) as cbatchproperty1,(cbatchproperty10) as cbatchproperty10,(cbatchproperty2) as cbatchproperty2,(cbatchproperty3) as cbatchproperty3,(cbatchproperty4) as cbatchproperty4,(cbatchproperty5) as cbatchproperty5,(cbatchproperty6) as cbatchproperty6,(cbatchproperty7) as cbatchproperty7,(cbatchproperty8) as cbatchproperty8,(cbatchproperty9) as cbatchproperty9,(cexpirationdate) as cexpirationdate,igrouptype,dexpirationdate,cgroupcode,cinvm_unit,iquantity,cunitid,cinva_unit,iinvexchrate,inum,iquotedprice,itaxunitprice,iunitprice,imoney,(Sales_FHD_W.iTaxRate) as bodytaxrate,itax,isum,inatunitprice,inatmoney,inattax,inatsum,iinvlscost,fsalecost,fsaleprice,kl,kl2,dkl1,dkl2,idiscount,inatdiscount,iretquantity,flossrate,crelacuscode,foutquantity,foutnum,(fsumsignquantity) as fsumsignquantity,(fSumSignNum) as fsumsignnum,frlossqty,isettlequantity,isettlenum,(iexchsum) as iexchsum,(imoneysum) as imoneysum,iqanum,iqaquantity,fulossqty,bqachecked,bqachecking,bqaneedcheck,bgsp,bqaurgency,(ccreditcuscode) as ccreditcuscode,(ccreditcusname) as ccreditcusname,ccuspaycond,icuscreline,iarmoney,ccrechpname,ccuspersoncode,ccushand,cpaycode,(bcredit) as bcredit,cpayname,(cgatheringplan) as cgatheringplan,(cgatheringplanname) as cgatheringplanname,(dcreditstart) as dcreditstart,(icreditdays) as icreditdays,(dgatheringdate) as dgatheringdate,(Sales_FHD_T.cSoCode) as headsocode,(Sales_FHD_T.iTaxRate) as headtaxrate,cmaker,cverifier,dkeepdate,cbaccounter,ccloser,csccode,ccuspostcode,cscname,ccusphone,ccusperson,(cdeliverunit) as cdeliverunit,(caddcode) as caddcode,cshipaddress,(ccontactname) as ccontactname,(cofficephone) as cofficephone,(cmobilephone) as cmobilephone,(case when binvbatch=1 then '是' else '否' end) as binvbatch,(case when binvquality=1 then '是' else '否' end) as binvquality,btrack,citem_class,citem_cname,citemcode,citemname,(Sales_FHD_T.cMemo) as cmemo,(funsignquantity) as funsignquantity,bsigncreate,bsignover,bneedloss,bneedsign,(funsignnum) as funsignnum,icreditstate,ireturncount,iswfcontrolled,iverifystate,(case when isnull(iswfcontrolled,0)=0 and isnull(cverifier,N'')=N'' then 100 when  isnull(iswfcontrolled,0)=0 and isnull(cverifier,N'')<>N'' then 2 else isnull(iverifystate,0) end) as iverifystatus,cfree1,cfree2,cfree3,cfree4,cfree5,cfree6,cfree7,cfree8,cfree9,cfree10,cinvdefine1,cinvdefine2,cinvdefine3,cinvdefine4,cinvdefine5,cinvdefine6,cinvdefine7,cinvdefine8,cinvdefine9,cinvdefine10,cinvdefine11,cinvdefine12,cinvdefine13,cinvdefine14,cinvdefine15,cinvdefine16,cdefine1,cdefine2,cdefine3,cdefine4,cdefine5,cdefine6,cdefine7,cdefine8,cdefine9,cdefine10,cdefine11,cdefine12,cdefine13,cdefine14,cdefine15,cdefine16,cdefine22,cdefine23,cdefine24,cdefine25,cdefine26,cdefine27,cdefine28,cdefine29,cdefine30,cdefine31,cdefine32,cdefine33,cdefine34,cdefine35,cdefine36,cdefine37,ccusdefine1,ccusdefine2,ccusdefine3,ccusdefine4,ccusdefine5,ccusdefine6,ccusdefine7,ccusdefine8,ccusdefine9,ccusdefine10,ccusdefine11,ccusdefine12,ccusdefine13,ccusdefine14,ccusdefine15,ccusdefine16,isosid,ibatch,ivtid,ufts,(Sales_FHD_T.DLID) as dlid,icorid,idlsid,(case when isnull(bserial,0)=0 then 0 else abs(isnull(iquantity,0))-isnull(iinvsncount,0) end) as iusnount,(creasoncode) as creasoncode,(creasonname) as creasonname,(cvencode) as cvencode,(bcashsale) as bcashsale,(cchangememo) as cchangememo,(cchanger) as cchanger,(Sales_FHD_W.cMemo) as cbodymemo,(cgathingcode) as cgathingcode,(ioutgolden) as ioutgolden,cmodifier,dmoddate,dverifydate,dcreatesystime,dverifysystime,dmodifysystime,(cast(isnull(iquantity,0)-isnull(foutquantity,0) as decimal(26,9))) as WCSL,(case when isnull(bneedbill,0)=1 then (cast(isnull(iquantity,0)-isnull(fretqtywkp,0)-isnull(isettlequantity,0) as decimal(26,9))) else 0 end) as WKSL,(case when isnull(bneedbill,0)=1 then (cast(case when isnull(iquantity,0)=0 then isnull(isum,0)-isnull(fretsum,0)-isnull(isettlenum,0) else (isnull(iquantity,0)-isnull(fretqtywkp,0)-isnull(isettlequantity,0))*itaxunitprice end as decimal(26,2))) else 0 end) as WKJE,(case when isnull(bneedbill,0)=1 then (cast(case when isnull(iquantity,0)=0 then isnull(isum,0)-isnull(fretsum,0) else (isnull(iquantity,0)-isnull(fretqtywkp,0))*itaxunitprice end as decimal(26,2))) else 0 end-isnull(iexchsum,0)) as WSKYB,(case when isnull(bneedbill,0)=1 then (cast(case when isnull(iquantity,0)=0 then isnull(isum,0)-isnull(fretsum,0) else (isnull(iquantity,0)-isnull(fretqtywkp,0))*itaxunitprice end as decimal(26,2))) else 0 end*(case when isnull(isum,1)=0 then 0 else isnull(inatsum,0)/isnull(isum,1) end)-isnull(imoneysum,0)) as WSKBB,(caccode) as caccode,(cacname) as cacname,(cdemandcode) as cdemandcode,(cdemandid) as cdemandid,(cdemandmemo) as cdemandmemo,(idemandseq) as idemandseq,(idemandtype) as idemandtype,(dblpreexchmomey) as dblpreexchmomey,(dblpremomey) as dblpremomey,dlossdate,dlosstime,clossmaker,crelacusname,isettletype,cinvoicecompany,cinvoicecompanyabbname,bmustbook,iprintcount,cscloser,extend_t_ccuscode_customer_icuscreline,extend_b_cinvcode_v_inventory_cinvaddcode,extend_t_ccuscode_customer_cdccode,extend_b_cinvcode_v_inventory_cposition,extend_t_ccuscode_customer_ccusname,extend_t_ccuscode_customer_ccccode,extend_b_cinvcode_v_inventory_ccomunitcode from #divTable3ED2FB6CA322401588E11889AFFF7283 Inner Join Sales_FHD_T inner join Sales_FHD_W on Sales_FHD_T.dlid=Sales_FHD_W.dlid  left join <font color="#ff0000">(select cInvAddCode as extend_b_cinvcode_v_inventory_cinvaddcode,cPosition as extend_b_cinvcode_v_inventory_cposition,cinvcode as keyextend_b_cinvcode_v_inventory_cinvcode from v_inventory) extend_b_cinvcode_v_inventory</font> on keyextend_b_cinvcode_v_inventory_cinvcode=Sales_FHD_W.cinvcode left join (select cCusName as extend_t_ccuscode_customer_ccusname,cCCCode as extend_t_ccuscode_customer_ccccode,cDCCode as extend_t_ccuscode_customer_cdccode,iCusCreLine as extend_t_ccuscode_customer_icuscreline,ccuscode as keyextend_t_ccuscode_customer_ccuscode from customer) extend_t_ccuscode_customer on keyextend_t_ccuscode_customer_ccuscode=Sales_FHD_T.ccuscode On #divTable3ED2FB6CA322401588E11889AFFF7283.tmpPrimaryID = Sales_FHD_W.idlsid where divID>0 and divID<=5000  order by cdlcode ASC

复制代码

记住了，在跟踪到的语句里，看到select * from select时，基本就是这句sql出了问题。
出问题的查询是个临时表有关查询，正好有建表语句，果断执行测试。

建表后执行查询，发现是少了这个字段，仔细看语句发现这个字段前面的几个字段，出现在了连接查询的实体列里，我的天，第一反应是改不了，查询语句是程序写好的。但是往前看语句时我发现了新大陆
select cextendfield,cextendjoin from voucherextendinfo where cardnumber='01' and cextendtype='L'
这条语句的查询结果里有了新发现

cextendfield居然是出错语句拼接查询的字段，而cextendjoin居然是连接查询的内容，哈哈，有救了,连接查询加上字段就行了。
update voucherextendinfo set cextendjoin='select cInvAddCode as extend_b_cinvcode_v_inventory_cinvaddcode,cPosition as extend_b_cinvcode_v_inventory_cposition,cinvcode as keyextend_b_cinvcode_v_inventory_cinvcode，ccomunitcode as extend_b_cinvcode_v_inventory_ccomunitcode from v_inventory' where cardnumber='01' and cextendtype='L'

执行完查询发货单列表，问题解决。

bachelor66

确实很贵啊，买不起啊，等LZ降价。                                                                                       

ufsoft888999

我的是U8V11.0的销售管理发货单列表打开后没有明细内容都是空行，底下有合计金额和总共几行记录数显示。我购买了本帖的解决方案代码去执行代码后打开发货单列表还是一样空白的没有明细内容显示，不知为何，请楼主速帮忙测试一下再给出解决方案，谢谢。

jojo_bj

ufsoft888999 发表于 2015-5-24 21:29
我的是U8V11.0的销售管理发货单列表打开后没有明细内容都是空行，底下有合计金额和总共几行记录数显示。我 ...

每个报错都可能有很多种情况，本帖只是给了其中一种的解决问题的思路而已，不一样的环境问题是不一样的

pizazzboy

有点贵，虽然很想看看                                             

jojo_bj

nazi8888 发表于 2015-11-13 11:15
为什么要用金币买？？不要金币，普及专业知识多好？？

你应该不在乎这点金币吧，毕竟每个月回帖那么多

收取金币你就当是为了给论坛赚人气，不回帖不发帖的是没金币收货的，学不了知识

wgq2541

大神出品 逢见必赞{:soso_e179:}

wgq2541

大神出品 逢见必赞{:soso_e179:}

wgq2541

大神出品 逢见必赞{:soso_e179:}

crhst

大神出品 逢见必赞{:soso_e184:}

37724861

流弊丢丢的兔神，我来膜拜学习下

lihuaren007

流弊的兔神，我来膜拜学习下

八鼎

呵呵  学习了   

wht896

看看楼主提供的什么东西

frogwong

大神出品 逢见必赞

酱油瓶子

太贵啊 不忍心下手

kouly2

真贵啊，果断不买。

yim0826

..........................

pizazzboy

大神，很想跟你学习啊，请告知QQ号吧，谢谢啊