NC57 查询引擎SQL手工设计报表开发

一、定义查询参数

二、写查询执行SQL

select distinct  ts_payapply.billno         as billno,
        ts_payapply.wish_paydate wish_paydate,
       (case when  ts_payapply.vbillstatus=0 then '审批未通过' 
            when ts_payapply.vbillstatus=1 then '审批通过' 
            when ts_payapply.vbillstatus=2 then '审批进行中'
            when ts_payapply.vbillstatus=3 then '提交状态'
            when ts_payapply.vbillstatus=8 then '自由态'
            when ts_payapply.vbillstatus=5 then '提交状态'
            when ts_payapply.vbillstatus=11 then '单据支付中'
            when ts_payapply.vbillstatus=12 then '付款成功'
      end )  vbillstatus,
       ts_payapply_b.fph          as fph,
       cubasdoc.custname custname,
       djzb.djbh               as djbh,
       ts_payapply_b.ret_datetime as ret_datetime,
       ts_payapply_b.ret_message  as ret_message
  from ts_payapply
 inner join ts_payapply_b
    on ts_payapply.pk_payapply = ts_payapply_b.pk_payapply and nvl(ts_payapply.dr,0)=0
  left  join (select * from  djfb where nvl(dr,0)=0 and djdl='fk' and dwbm=#LoginCorp#) djfb
    on ts_payapply_b.pk_payapply_b = djfb.ddhh and
       nvl(ts_payapply_b.dr, 0) = 0 and nvl(djfb.dr, 0) = 0
       and ts_payapply_b.pk_payapply = djfb.ddlx
  left  join  (select * from  djzb where nvl(dr,0)=0 and djdl='fk' and dwbm=#LoginCorp#) djzb
    on (djfb.djdl = 'fk' and nvl(djzb.dr, 0) = 0 and djfb.vouchid = djzb.vouchid)
  inner join cubasdoc on   ts_payapply_b.pk_cubasdoc = cubasdoc.pk_cubasdoc
where ( ts_payapply_b.pk_cubasdoc in  (#supplier#)  or 
 (  ' '= ( select a from ( select  #supplier# a , rownum from dual ) where a =' ' )) ) 
 and (ts_payapply.billno =#billno# or #billno#  is null) 
 and ( ts_payapply.wish_paydate >=#wish_paydate# or #wish_paydate#  is null )
 order by  wish_paydate desc

 注：1、查询参数多选，SQL手工设计使用的查询参数是替代参数，即把查询参数直接替换到sql语句里面，所以 #supplier# is                    null 在多选情况下就会变成 '12312312','234wereq32' is null ,造成sql语法错误，操作符选择=时，则不会存在语法问题            2、order by 字段是在查询视图返回结果集以后再执行的，不能写 order by ts_payapply.wish_paydate desc