用友股份-LE服务支持部技术方案--《查询引擎手工SQL间带参数多条件穿透》建立日期：2013-06-01修改日期：xxxx-xx-xx文档属性：客户文控编号：LE-DY-JS-2013-00642/9文档控制创建记录审阅人姓名所属部门职位审阅签字发布人姓名所属部门发布时间日期作者所属部门邮件地址版本2013-06-01冯海涛V1.03目录查询引擎手工SQL间带参数多条件穿透...............................................................................44查询引擎手工SQL间带参数多条件穿透手工SQL查询间做穿透，而且是多值穿透，还需要把汇总查询中的参数传递到明细查询中的参数，可参考该文档设置。汇总查询参数查询代码selectbd_glorgbook.glorgbookcode,t.subjcode,t.dispname,bd_defdoc.doccodeasdoccode,bd_defdoc.docnameasdocname,sum(abs(localdebitamount-localcreditamount))fse,sum(abs(localdebitamount-localcreditamount)*5bd_defdoc.docname)llz,sum(abs(nextlocaldebitamount-nextlocalcreditamount))sjz,sum(abs(localdebitamount-localcreditamount)*bd_defdoc.docname)-sum(abs(nextlocaldebitamount-nextlocalcreditamount))cefrom(selectgl_detail.pk_corp,gl_detail.pk_glorgbook,gl_detail.prepareddatev,gl_detail.pk_voucher,gl_detail.detailindex,gl_detail.assid,bd_accsubj.subjcodeassubjcode,bd_accsubj.dispnameasdispname,gl_detail.localdebitamountlocaldebitamount,gl_detail.localcreditamountaslocalcreditamount,lead(localdebitamount,1,0)OVER(ORDERBYgl_detail.pk_glorgbook,prepareddatev,gl_detail.pk_voucher,detailindex)ASnextlocaldebitamount,lead(localcreditamount,1,0)OVER(ORDERBYgl_detail.pk_glorgbook,prepareddatev,gl_detail.pk_voucher,detailindex)ASnextlocalcreditamountfromgl_voucherinnerjoingl_detailongl_voucher.pk_voucher=gl_detail.pk_voucherinnerjoinbd_accsubjongl_detail.pk_accsubj=bd_accsubj.pk_accsubjwheregl_detail.pk_corp=#LoginCorp#andgl_detail.dr=0andgl_voucher.dr=0andgl_detail.pk_glorgbook=#param3#andgl_detail.prepareddatev>=#param1#andgl_detail.prepareddatev<=#param2#and(gl_detail.errmessageisnullorgl_detail.errmessage2<>'暂存')orderbygl_detail.pk_glorgbook,prepareddatev,6gl_detail.pk_voucher,detailindex)tinnerjoingl_freevalueont.assid=gl_freevalue.freevalueidinnerjoinbd_defdocongl_freevalue.checkvalue=bd_defdoc.pk_defdocinnerjoinbd_glorgbookont.pk_glorgbook=bd_glorgbook.pk_glorgbookwherebd_defdoc.shortname<>'无'groupbybd_glorgbook.glorgbookcode,subjcode,dispname,doccode,docnameorderbysubjcodeasc明细查询selectbd_glorgbook.glorgbookcode,t.prepareddatev,t.no,t.subjcode,t.dispname,bd_defdoc.doccodeasdoccode,bd_defdoc.docnameasdocname,sum(abs(localdebitamount-localcreditamount))fse,sum(abs(localdebitamount-localcreditamount)*bd_defdoc.docname)llz,sum(abs(nextlocaldebitamount-nextlocalcreditamount))sjz,sum(abs(localdebitamount-localcreditamount)*bd_defdoc.docname)-sum(abs(nextlocaldebitamount-nextlocalcreditamount))ce传递的接收参数穿透过来的参数7from(selectgl_detail.pk_corp,gl_detail.pk_glorgbook,gl_detail.prepareddatev,gl_detail.pk_voucher,gl_voucher.no,gl_detail.detailindex,gl_detail.assid,bd_accsubj.subjcodeassubjcode,bd_accsubj.dispnameasdispname,gl_detail.localdebitamountlocaldebitamount,gl_detail.localcreditamountaslocalcreditamount,lead(localdebitamount,1,0)OVER(ORDERBYgl_detail.pk_glorgbook,prepareddatev,gl_detail.pk_voucher,detailindex)ASnextlocaldebitamount,lead(localcreditamount,1,0)OVER(ORDERBYgl_detail.pk_glorgbook,prepareddatev,gl_detail.pk_voucher,detailindex)ASnextlocalcreditamountfromgl_voucherinnerjoingl_detailongl_voucher.pk_voucher=gl_detail.pk_voucherinnerjoinbd_accsubjongl_detail.pk_accsubj=bd_accsubj.pk_accsubjwheregl_detail.pk_corp=#param6#--此处需设置为参数传递，不能写LoginCorpandgl_detail.dr=0andgl_voucher.dr=0and(gl_detail.errmessageisnullorgl_detail.errmessage2<>'暂存')orderbygl_detail.pk_glorgbook,prepareddatev,gl_detail.pk_voucher,detailindex)tinnerjoingl_freevalueont.assid=gl_freevalue.freevalueid8innerjoinbd_defdocongl_freevalue.checkvalue=bd_defdoc.pk_defdocinnerjoinbd_glorgbookont.pk_glorgbook=bd_glorgbook.pk_glorgbookwherebd_defdoc.shortname<>'无'andbd_glorgbook.glorgbookcode=#param4#anddoccode=#param3#andt.subjcode=#param5#andt.prepareddatev>=#param1#andt.prepareddatev<=#param2#groupbybd_glorgbook.glorgbookcode,prepareddatev,t.no,subjcode,dispname,doccode,docnameorderbysubjcodeasc两个查询间需要穿透多个字段在汇总查询中的穿透代码设置。9由于日期也需要给被穿透查询，所以要在汇总查询中设置日期参数，然后在明细查询中也要设置完全一致的参数，这样在查询时就会把汇总的查询参数默认带到明细查询的参数中。另外，被穿透的手工sql中有#LoginCorp#被调用时不能解析，所以需要在主查询中设置，然后传递到被穿透查询中。
