sql for lagging over a calculated column

assume you have the following data:

id	crtl	c1	c2	c3	result
1	1	12	-1	-1	12
2	2	-1	7	4	12+7=19
3	2	-1	9	8	19+9=28
4	3	-1	5	7	28+7=35
5	1	18	2	3	18

crtl defines how to calculate the result:
1 reset with c1
2 old val of result + c2
3 old val of result + c3

with the help of the model clause you can get the result, even with a readable statement.


SQL> select crtl,c1,c2,c3, result
from (select * from mytest order by id)
model
dimension by (id)
measures (crtl,c1,c2,c3,result)
rules (
result[ANY] = case crtl[cv()]
when 1 then c1[cv()]
when 2 then result[cv()-1]+c2[cv()]
when 3 then result[cv()-1]+c3[cv()]
else 0 end
)
order by id;

CRTL C1 C2 C3 RESULT
---------- ---------- ---------- ---------- ----------
1 12 -1 -1 12
2 -1 7 4 19
2 -1 9 8 28
3 -1 5 7 35
1 18 2 3 18





(thanks to rob van wijk for this)