Transformer and exists en MERGE INTO
Bonjour,
je veux transformer cette requette trop lente en Merge into mais j'ai deux conditions and exists comment je peut transformer la requette correctement
requette initiale
Code:
1 2 3 4 5 6 7 8 9 10 11
| UPDATE DR_POS pos
set MtIntInterPer = (select max(MtFlx) from DR_ECH_OPE ech
where ech.DateCurrent = '03/01/2019' AND ech.IdOpe = pos.IdOpe AND ech.IdJmb = pos.IdJmb AND ech.CdTypOpe = pos.CdTypOpe AND ech.CdTypFlx in ('INT', 'IPR')
and ech.DtVal=pos.DtProPaiInt)
where datecurrent = '03/01/2019' AND CdEtab = 'BPCE'
AND exists (select 1 from DR_ECH_OPE ech
where ech.DateCurrent = '03/01/2019' AND ech.IdOpe = pos.IdOpe AND ech.IdJmb = pos.IdJmb AND ech.CdTypOpe = pos.CdTypOpe AND ech.CdTypFlx in ('INT', 'IPR')
and ech.DtVal=pos.DtProPaiInt )
AND exists (select 1 from DR_ECH_OPE ech
where ech.DateCurrent = '03/01/2019' AND ech.IdOpe = pos.IdOpe AND ech.IdJmb = pos.IdJmb AND ech.CdTypOpe = pos.CdTypOpe AND ech.CdTypFlx in ('INT', 'IPR')
and DtFinPer=pos.DTARRETE); |
ma requette que j'ai essayé de tester mais elle me renvoie pas le même nombre de ligne:
Code:
1 2 3 4 5 6 7 8 9 10
| Merge into DR_POS pos
using (select max(ech.MtFlx) max_mtflx ,ech.DateCurrent,ech.IdOpe,ech.IdJmb ,ech.CdTypOpe,ech.CdTypFlx,ech.DtVal, ech.DtFinPer from DR_ECH_OPE ech
where ech.DateCurrent = '03/01/2019' AND ech.CdTypFlx in ('INT', 'IPR') group by ech.DateCurrent, ech.IdOpe, ech.IdJmb, ech.CdTypOpe, ech.CdTypFlx,
ech.DtVal, ech.DtFinPer ) ech
on((pos.datecurrent = '03/01/2019' AND pos.CdEtab = 'BPCE' and ech.IdOpe = pos.IdOpe AND ech.IdJmb = pos.IdJmb AND ech.CdTypOpe = pos.CdTypOpe
and ech.DtVal=pos.DtProPaiInt ) and (ech.DateCurrent = '03/01/2019' AND ech.IdOpe = pos.IdOpe AND ech.IdJmb = pos.IdJmb AND ech.CdTypOpe = pos.CdTypOpe AND ech.CdTypFlx in ('INT', 'IPR')
and ech.DtFinPer=pos.DTARRETE))
when matched then
update set MtIntInterPer=max_mtflx ; |
Merci