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 : Sélectionner tout - Visualiser dans une fenêtre à part
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 : Sélectionner tout - Visualiser dans une fenêtre à part
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