BankCoopReportMapper.xml_mapper.xml能用nvl函数吗-优快云博客

本文链接：https://blog.youkuaiyun.com/lzljava/article/details/107683878

本文详细解析了银行合作还款计划表的SQL查询语句，涵盖了贷款申请信息、客户资料、还款详情及状态等关键字段，展示了如何通过复杂的条件筛选实现精准的数据查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

<select id = "findRepayCooperAtionInfo" parameterType="java.util.HashMap" resultType="java.util.HashMap">
       select
       o.id_order as "applyNo",
       po.name as "name",
       (select CODE_VALUE from PLOAN_CODE_LIBRARY where CODE_TYPE='channel_no' and CODE_NO=o.channel_no) as "channelNo",
       pli.PRODUCT_NAME as "productName",
       case
           when o.CHANNEL_NO ='red' then (select CODE_VALUE from PLOAN_CODE_LIBRARY where (CODE_TYPE='intent_loan_plan' and CODE_NO=o.lending_type))
else
(select CODE_VALUE from PLOAN_CODE_LIBRARY where (CODE_TYPE='loan_type' and CODE_NO=o.loan_type))
end as "loanType",
       l.code_value AS "loanPurpose",
       nvl((to_char(o.cust_apply_time,'yyyy-mm-dd')),' ') AS "applyTime",
       (select bad.division_name from bankcoop_admin_division bad where bad.division_code = o.REQ_PROVINCE) as "reqProvince",
   (select bad.division_name from bankcoop_admin_division bad where bad.division_code = o.REQ_CITY) as "reqCity",
       to_char((c.loan_amt), 'fm99999999990.00') as "loanAmt",
       to_char((nvl(o.loan_amount,0)), 'fm99999999990.00') as "reqCapital",
       p.RPTERM as "rpTerm",
       case
          when o.monthly_fee_rate is not null then to_char(o.monthly_fee_rate,'fm999990.099')||'%'
       else to_char(o.monthly_fee_rate,'fm999990.099') end AS "monthFeeRate",
       case
   when c.channel_no ='jiujiang' then to_char(nvl(p.PAY_FEE,0),'fm99999999990.00')
   when c.channel_no ='mtb'
       then to_char(nvl(o.monthly_fee_rate/100,0) * nvl(c.loan_amt,0),'fm99999999990.00')
   when c.channel_no ='pds' then to_char(nvl(pf.PAY_AMOUNT,0),'fm99999999990.00')
   else to_char(nvl(p.PAY_FEE,0),'fm99999999990.00')
        end as "handfeeMth",
       case
          when o.MONTHLY_RATE is not null then to_char(nvl(o.MONTHLY_RATE,0),'fm999990.099')||'%'
   else to_char(nvl(c.RATE,0)/12,'fm999990.099') end AS "monthRate",

       to_char(p.PAY_CAPITAL, 'fm99999999990.00') as "capital",
       to_char(p.pay_aint, 'fm99999999990.00') as "aInt",
       case
          when c.RATE is not null then to_char(nvl(c.RATE,0),'fm999990.099')||'%'
       else to_char(nvl(o.MONTHLY_RATE * 12,0),'fm999990.099') end AS "loanRate",
       case
           when c.channel_no ='jiujiang' then nvl(service.FEE_AMOUNT,0)
           when c.channel_no ='mtb' then nvl(service.FEE_AMOUNT,0)
           when c.channel_no ='pds' then nvl(pfs.PAY_AMOUNT,0)
           else nvl(service.FEE_AMOUNT,0)
       end as "serviceFee" ,
       case
          when o.service_fee_rate is not null then to_char(o.service_fee_rate,'fm999990.099')||'%'
   else to_char(o.service_fee_rate,'fm999990.099') end AS "oneFeeRate",

       '--' as "repayPenalty",
       '--' AS "penaltyRate",

       o.term as "loanTerm",
       substr(c.loan_time, 1, 8) as "putoutDate",
       p.PAY_DATE as "repayDate",
       (select code_value from ploan_code_library where code_type ='repay_type' and code_no = o.REPAY_TYPE) as "repaymentType",
           case
   when c.channel_no ='mtb' and cur.PAY_DATE < to_char(sysdate-1,'yyyyMMdd') and cur.PAY_CAPITAL > cur.ACTUAL_CAPITAL then '逾期'
   else decode(p.status,'00','初始化','01','正常','02','逾期','03','代扣中','04','结清','05','提前结清','07','代偿中','09','代偿结清','10','追偿结算')
        end as "status",

       o.manager_name as "managerName",
       o.MANAGER_UM as "managerUM",
       o.MANAGER_ORG_NAME as "managerOrgName",
       o.manager_org as "parterOrg"
       from BANKCOOP_LOAN_REPAY_PLAN p
       left join BANKCOOP_LOAN c on c.bank_loan_no=p.bank_loan_no
       left join ploan_order o on c.apply_no=o.id_order
       left join PLOAN_LPRODUCT_INFO pli on (pli.product_no = o.product_type and pli.PRODUCT_STATUS = '003000')
       left join ploan_order_cust_person po on po.id_cust=o.id_cust
       LEFT JOIN ploan_code_library l1 on l1.code_type = 'loan_type' and l1.code_no = o.loan_type
       LEFT JOIN ploan_code_library l2 on l2.code_type = 'intent_loan_plan' and l2.code_no = o.lending_type
       LEFT JOIN ploan_code_library l ON o.loan_purpose = l.code_no AND l.code_type = 'loan_purpose'
       LEFT JOIN CORE_CONTRACT_INFO cci on cci.apply_no = c.apply_no
       LEFT JOIN core_contract_source_fund csf on csf.contract_no=cci.contract_no
       Left join bankcoop_loan_feeplan f on(f.loan_no=c.bank_loan_no and f.fee_code='HAND_FEE_MTH' and f.term=p.rpterm)
       Left join bankcoop_loan_feeplan pf on(pf.loan_no=c.bank_loan_no and pf.fee_code='HAND_FEE_MTH' and to_char(pf.PAY_DATE,'yyyyMMdd')=p.PAY_DATE)
       Left join bankcoop_loan_feeplan pfs on(pfs.loan_no=c.bank_loan_no and pfs.fee_code='PFM_FEE_ONE')
       left join (
       select bl.FEE_AMOUNT as FEE_AMOUNT,
       bl.BANK_LOAN_NO as bank_loan_no
       from BANKCOOP_LOAN bl
       left join bankcoop_loan_feeplan pf
       on (pf.loan_no=bl.bank_loan_no and pf.fee_code='PFM_FEE_ONE')
       ) service on (service.bank_loan_no = p.BANK_LOAN_NO )
       left join (
           select p.BANK_LOAN_NO,p.RPTERM,p.PAY_CAPITAL,p.ACTUAL_CAPITAL,p.PAY_DATE from BANKCOOP_LOAN_REPAY_PLAN p
           )cur on (cur.BANK_LOAN_NO = p.BANK_LOAN_NO and cur.RPTERM = p.RPTERM)
       where (c.state = '1' and p.PAY_DATE is not null)
       
       <if test = "channelNoMap!=null and channelNoMap.size > 0">
       and o.CHANNEL_NO in
       <foreach item="channelNo" collection="channelNoMap" open="(" separator="," close=")">
               #{channelNo}
       </foreach>
       </if>
       
       <if test="productMap!=null and productMap.size > 0">
           and pli.product_no in
           <foreach collection="productMap" item="procode" open="(" separator="," close=")">
               #{procode}
           </foreach>
       </if>
       
       <if test="name!=null and name!=''">
           and po.name =#{name,jdbcType=VARCHAR}
       </if>
       
       <if test="managerOrgName!=null and managerOrgName!=''">
           and o.MANAGER_ORG_NAME =#{managerOrgName,jdbcType=VARCHAR}
       </if>
       
       <if test="parterOrg!=null and parterOrg!=''">
           and o.manager_org =#{parterOrg,jdbcType=VARCHAR}
       </if>
       
       <if test="managerName!=null and managerName!=''">
           and o.manager_name =#{managerName,jdbcType=VARCHAR}
       </if>
       
       <if test="reqProvince!=null and reqProvince!=''">
           and o.REQ_PROVINCE =#{reqProvince,jdbcType=VARCHAR}
       </if>
       
       <if test="reqCity!=null and reqCity!=''">
           and o.REQ_CITY =#{reqCity,jdbcType=VARCHAR}
       </if>
       <if test="startTime!=null and startTime!=''">
           and substr(c.loan_time, 1, 8) >=#{startTime,jdbcType=VARCHAR}
       </if>
       <if test="endTime!=null and endTime!=''">
           and substr(c.loan_time, 1, 8) <=#{endTime,jdbcType=VARCHAR}
       </if>
           order by c.loan_time desc,p.BANK_LOAN_NO,to_number(p.RPTERM) asc
   </select>