CODEWITHSUNDEEP
sqloracle11g
szakwani
szakwani

Reputation: 384

In Oracle how do you perform a calculation depending on the previous calculated result

I would like to perform a simple calculation as follows

Dataset

SR      value_one      result 
1         null          0.99 
2          1            0.99*1 = 0.99 
3         0.75          0.99*0.75 = 0.7425 
4         0.75          0.7425*0.75 = 0.556875 
5          1            0.556875*1 = 0.556875 
6          1            0.556875*1 = 0.556875 
7          1            0.556875*1 = 0.556875 
8          1            0.556875*1 = 0.556875 
9          1            0.556875*1 = 0.556875 
10         1            0.556875*1 = 0.556875

The results is dependent on the previous one for SR >= 2 while always starts with 0.99.

Looping while performing a calculation. Database Oracle.

Upvotes: 1

Views: 127

Answers (1)

guthy
guthy

Reputation: 326

If you wanted to calculate a running sum, it would be easy to do using analytic functions:

with d as (
  select 1 as sr, cast(null as number) as value_one from dual union all 
  select 2 as sr, 1 as value_one from dual union all 
  select 3 as sr, 0.75 as value_one from dual union all 
  select 4 as sr, 0.75 as value_one from dual union all 
  select 5 as sr, 1 as value_one from dual union all 
  select 6 as sr, 1 as value_one from dual union all 
  select 7 as sr, 1 as value_one from dual union all 
  select 8 as sr, 1 as value_one from dual union all 
  select 9 as sr, 1 as value_one from dual union all
  select 10 as sr, 1 as value_one from dual 
)
select d.*, sum(nvl(value_one, 0.99)) over (order by sr)
from d;

Unfortunately, there is no "PRODUCT" aggregate function which we could use here, so we have to make a detour using EXP and LN (see https://stackoverflow.com/a/3912248/1230592):

with d as (
  select 1 as sr, cast(null as number) as value_one from dual union all 
  select 2 as sr, 1 as value_one from dual union all 
  ..
)
select d.*, nvl(exp (sum (ln (value_one)) over (order by sr)), 1) * 0.99
from d;

This should give you the desired results

Upvotes: 2

Related Questions