select
a.DAMAGESTARTDATE
,a.endcasedate
,a.COMCODE
,c.CLAIMCODE
--,c.endCaseCode
,tt.validno
,a.registno
,a.policyno
,a.CLAIMNO
,to_char(b.damagedate,'yyyymmdd')||substr(b.damagehour,1,2)||substr(b.damagehour,4,2) as DAMAGEDtime
,case when b.reportdate is not null and substr(b.reportdate,12) !='00:00:00' then to_char(b.reportdate,'yyyymmddhh24mi')
when b.reportdate is not null and substr(b.reportdate,12) ='00:00:00' then to_char(b.reportdate,'yyyymmdd')||substr(b.reporthour,1,2)||substr(b.reporthour,4,2)
else '' end as REPORTTIME
,to_char(a.claimdate,'yyyymmddhh24mi') as claimtime
,to_char(d.UNDERWRITEENDDATE,'yyyymmddhh24mi') as UNDERLOSStime
,to_char(a.ENDCASEDATE,'yyyymmddhh24mi') as ENDCASEtime
,nvl(d.IndemnityDuty,'9') as IndemnityDuty
,nvl(e.InsureAccident,'100') as InsureAccident
,m.TRANSCODE as damagecode
,nvl(d.sumpaid,0)-nvl(f.sumrealpay,0)-nvl(g.sumrealpay,0)-nvl(h.PLANFEE,0) as sumpaid --20250218 1.交强的赔款金额中加了费用和无责带赔 赔款金额·:prplcompensate.SumDutyPaid-prpLcharge.SumRealPay-PrpLloss.SumRealPay
,case when a.claimno in('508072023011301007804','508072024011301005295') then 0 else f.sumrealpay end
,g.sumrealpay as NOFAULTPAID
,i_enddate as STATDATE
,sysdate as update_time
,'endcase' as flag
from O_prplclaim a
left join o_prplregist b on(a.registno =b.registno )
left join O_PRPIIACCLAIMDEMAND c ON(a.registno =c.registno and a.POLICYNO=c.POLICYNO)
left join (select max(UNDERWRITEENDDATE) as UNDERWRITEENDDATE,
max(IndemnityDuty) as IndemnityDuty,
sum(sumpaid) as sumpaid,
REGISTNO,
POLICYNO,
claimno
from O_prplcompensate
where UNDERWRITEFLAG IN('1','3') and trunc(UNDERWRITEENDDATE) <= i_enddate
group by
REGISTNO,
POLICYNO,
claimno
)d ON(a.REGISTNO = d.REGISTNO and a.POLICYNO=d.POLICYNO and a.claimno =d.claimno )
left join (select max(InsureAccident) as InsureAccident,
REGISTNO,riskcode
from O_prplcheck
group by REGISTNO,riskcode
)e on(a.REGISTNO = e.REGISTNO and e.riskcode like '%0807%')
left join (select sum(sumrealpay) as sumrealpay,
b.claimno from o_prplcharge a ,O_prplcompensate b
where a.COMPENSATENO = b.COMPENSATENO
and b.UNDERWRITEFLAG IN('1','3') and trunc(b.UNDERWRITEENDDATE) <= i_enddate
group by b.claimno
)f on(a.claimno=f.claimno )
left join (select sum(sumrealpay) as sumrealpay,
b.claimno
from o_prplloss a,O_prplcompensate b
where a.COMPENSATENO = b.COMPENSATENO and a.FeeTypeCode=94
and b.UNDERWRITEFLAG IN('1','3') and trunc(b.UNDERWRITEENDDATE) <= i_enddate
group by b.claimno
)g on(a.claimno=g.claimno )
left join o_UtiTransCode m on (b.damagecode=m.sourceCode
and m.validFlag = '1'
and m.comCode = '01'
and m.sourceType = 'Claim'
and m.transType = 'SINOSOFT_SALI'
and m.codeType = 'PlatFormDamageType')
left join o_guciinsurevalid tt on a.policyno=tt.policyno
left join (
select claimno,sum(PLANFEE) PLANFEE from o_prplpaymentdatamid
where feetypecode in('P90')
group by claimno
)h on a.CLAIMNO=h.claimno
where trunc(a.endcasedate) between TRUNC(i_enddate,'YYYY') and i_enddate
and a.validflag=1
and a.riskcode='0807'
and a.policyno not IN
(select a.policyno from f_policymain a,o_gupolicymain t
where
a.classcode='08'
AND a.mainpolicyno=t.policyno
AND (a.CARKINDCODE
in('J1','J2','J0','M0','M1','M2','M3','J5','J6','J3',
'J4','M5','M6','71','72','73','81','82','91','92')
OR t.newflag='0'
OR t.newflag is null)
) 优化一下这段SQL,速度运行太慢了,输出完整的优化后的SQL。
最新发布