This is a very complicated situation for me and I was wondering if someone can help me with it:
Here is my table:
In my table every value in the SQLCalculatedPmu column or desiredValue Column is calculated based on the preceding value.
As you can see, I have calculated the SQLcalculatedPMU column based on the round on 3 decimals. The case is that on each line radiation, the client want to start the next calculation based on 2 decimals instead of 3(represented in the desired values column). Next values will be recalculated. For example line 6 will change as the value in line 5 is now on 2 decimals. I could handle this if there where one single radiation but in my case I have a lot of Radiations and in this case they will change all based on the calculation of the two decimals.
In summary, Here are the steps:
| investor | | type | price | number | Solde | SQLCalculatedPmu | DesiredValues | |
|---|
10 | 2570088 | Insertion | 133 | 60 | 60 | 133 | 133
| |
| 10 | 2636476 | Insertion | 0 | 7 | 67 | 119,104 | 119,104
| |
| 10 | 2636477 | Insertion | 0 | 1 | 68 | 117,352 | 117,352
| |
| 10 | 2958292 | Insertion | 0 | 6 | 74 | 107,837 | 107,837
| |
| 10 | 3148350 | Radiation | 0 | -1 | 73 | 107,837 | 107,83 | |
| 10 | 3282189 | Insertion | 0 | 7 | 80 | 98,401 | 98,395
| |
| 10 | 3646066 | Insertion | 0 | 80 | 160 | 49,201 | 49,198
| |
| 10 | 3783510 | Insertion | 0 | 16 | 176 | 44,728 | 44,725
| |
| 10 | 3783511 | Insertion | 0 | 1 | 177 | 44,475 | 44,472
| |
| 10 | 4183663 | Insertion | 0 | 11 | 188 | 41,873 | 41,87
| |
| 10 | 4183664 | Insertion | 0 | 1 | 189 | 41,651 | 41,648
| |
| 10 | 4183665 | Radiation | 0 | -1 | 73 | 41,651 | 41,64
| |
| 10 | 4183666 | Insertion | 0 | 7 | 80 | 40,156 | 40,145
| |
| 10 | 4183667 | Insertion | 0 | 80 | 160 | 28,474 | 28,466
| |
| 10 | 4183668 | Insertion | 0 | 16 | 176 | 26,908 | 26,901
| |
| 10 | 4183669 | Insertion | 0 | 1 | 177 | 26,816 | 26,809
| |
| 10 | 4183670 | Insertion | 0 | 11 | 188 | 25,842 | 25,836
| |
| 10 | 4183671 | Insertion | 0 | 1 | 189 | 25,757 | 25,751 | |
In my table every value in the SQLCalculatedPmu column or desiredValue Column is calculated based on the preceding value.
As you can see, I have calculated the SQLcalculatedPMU column based on the round on 3 decimals. The case is that on each line radiation, the client want to start the next calculation based on 2 decimals instead of 3(represented in the desired values column). Next values will be recalculated. For example line 6 will change as the value in line 5 is now on 2 decimals. I could handle this if there where one single radiation but in my case I have a lot of Radiations and in this case they will change all based on the calculation of the two decimals.
In summary, Here are the steps:
1 - round the value of the preceding row of a raditaiton and put it in the radiation row.
2 - calculate all next insertion rows.
3 - when we reach another radiation we redo steps 1 and 2 and so on
Im using an oracle DB and I m the owner so I can make procedures, insert, update, select. But I m not familiar with procedures or loops.
For information, this is the formula for SQLCalculatedPmu uses two additional culmns price and number and this is calculated every line cumulativelly for each investor:
(price * number)+(cumulative (price*number) of the preceeding lines)
tried something like this :
update PMUTemp
set SQLCalculatedPmu =
case when Type = 'Insertion' then
(number*price)+lag(SQLCalculatedPmu ,1) over (partition by investor
order by Record_no)/
(number+lag(solde,1) over (partition by investor order by Record_no))
else
TRUNC(lag(SQLCalculatedPmu,1) over partition by invetor order by Record_no))
end;
but I gave me this error (I think it's because I m looking at the preceiding line that itself is modified during the SQL statement) :
ORA-30486 : window function are allowed only in the SELECT list of a query.
was wondering if creating a procedure that will be called as many time as the number of radiations would do the job but I m really not good in procedures
Any help Regards,