Here is my SQL to create and insert:
CREATE TABLE "SYSTEM"."PMUOrdinaireTMP111111" ( "IDENTIFIANTACTIONNAIRE" NUMBER(23,0), "NUMEROECRITURE" NUMBER(8,0), "SENSOPERATION" VARCHAR2(20 BYTE), "PRIXREVIENT" NUMBER(19,2), "NOMBRETITRES" NUMBER(15,0), "SOLDE" NUMBER(20,0) ) ; insert into PMUSALARIAUXTMP111111 values (10,1004848,'I',0,1,1); insert into PMUSALARIAUXTMP111111 values (10,1123692,'I',721,1,2); insert into PMUSALARIAUXTMP111111 values (10,1150237,'I',0,1,3); insert into PMUSALARIAUXTMP111111 values (10,1185011,'I',0,1,4); insert into PMUSALARIAUXTMP111111 values (10,1377533,'I',703,1,5); insert into PMUSALARIAUXTMP111111 values (10,1426786,'R',0,5,0); insert into PMUSALARIAUXTMP111111 values (10,1901940,'I',0,1,1); insert into PMUSALARIAUXTMP111111 values (10,2172420,'R',0,1,0); insert into PMUSALARIAUXTMP111111 values (10,2953958,'I',0,1,1);
Then I apply this SQL to get the desired result:
select IDENTIFIANTACTIONNAIRE, NUMEROECRITURE, SENSOPERATION, NOMBRETITRES, PRIXREVIENT, Solde, PrixRevDevOrigine, PrixRevEuro, CumulEuro, PMUEuro, PMUEuroArrondi, flag from PMUOrdinaireTMP111111 model partition by (IDENTIFIANTACTIONNAIRE) dimension by (row_number() over (partition by IDENTIFIANTACTIONNAIRE order by NUMEROECRITURE) rid) measures(NUMEROECRITURE, SENSOPERATION, PRIXREVIENT, NOMBRETITRES, Solde, 0 PrixRevDevOrigine, 0 PrixRevEuro, 0 CumulEuro, 0 PMUEuro, 0 PMUEuroArrondi, nvl(last_value(case when SENSOPERATION = 'R' then 'R' end ignore nulls ) over (partition by IDENTIFIANTACTIONNAIRE order by NUMEROECRITURE), 'I' ) flag ) rules sequential order ( PrixRevDevOrigine[any] order by NUMEROECRITURE = case when SENSOPERATION[cv()] = 'R' THEN 0 ELSE PRIXREVIENT[cv()] END, PrixRevEuro[any] order by NUMEROECRITURE = NombreTitres[cv()]*PrixRevDevOrigine[cv()], CumulEuro[any] order by NUMEROECRITURE = case WHEN Solde[cv()] = NombreTitres[cv()] and SENSOPERATION[cv()] = 'I' THEN NombreTitres[cv()]*PrixRevEuro[cv()] when SENSOPERATION[cv()] = 'I' THEN CumulEuro[cv()-1]+PrixRevEuro[cv()] WHEN SENSOPERATION[cv()] = 'R' THEN PMUEuroArrondi[cv()-1] END , PMUEuro[any] order by NUMEROECRITURE = case when Solde[cv()] = 0 THEN 0 ELSE CumulEuro[cv()]/Solde[cv()] END, PMUEuroArrondi[any] order by NUMEROECRITURE = trunc(PMUEuro[cv()],2)
I was hoping to get this result:
IdentifanActionnaire NumeroEcriture SensOperation NOMBRETITRES PRIXREVIENT Solde PrixRevDevOrigine PrixRevEuro CumulEuro PMUEuro PMUEuroArrondi flag ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 10 1004848 I 1 0 1 0 0 0 0 0 I 10 1123692 I 1 721 2 721 721 721 360,5 3 60,5 I 10 1150237 I 1 0 3 0 0 721 240,333333 240,33 I 10 1185011 I 1 0 4 0 0 721 180,25 180,25 I 10 1377533 I 1 703 5 703 703 1424 284,8 284,8 I 10 1426786 R 5 0 0 0 0 0 284,8 284,8 R 10 1901940 I 1 0 1 0 0 0 0 0 R 10 2172420 R 1 0 0 0 0 0 0 0 R 10 2953958 I 1 0 1 0 0 0 0 0 R
But I get this:
IdentifanActionnaire NumeroEcriture SensOperation NOMBRETITRES PRIXREVIENT Solde PrixRevDevOrigine PrixRevEuro CumulEuro PMUEuro PMUEuroArrondi flag ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 10 1004848 I 1 0 1 0 0 0 0 0 I 10 1123692 I 1 721 2 721 721 721 360,5 360,5 I 10 1150237 I 1 0 3 0 0 721 240,333333 240,33 I 10 1185011 I 1 0 4 0 0 721 180,25 180,25 I 10 1377533 I 1 703 5 703 703 1424 284,8 284,8 I 10 1426786 R 5 0 0 0 0 0 0 0 R 10 1901940 I 1 0 1 0 0 0 0 0 R 10 2172420 R 1 0 0 0 0 0 0 0 R 10 2953958 I 1 0 1 0 0 0 0 0 R
I think the reason is that CumulEuro calls PMUEuroArrondi and also PMUEuro calls CumulEuro when the column "SensOperation" = R. It is as simple as when "SensOperation" = R, then I only trim the previous value of PMUEuroArrondi.