Hi Experts,
I am trying to solve a problem. It is possible through other methods but I specifically want to explore and learn this using MODEL Clause.
But facing some problem -
WITH t(i, col1, col2, col3) AS(
SELECT 1,10,10,8 FROM dual UNION ALL
SELECT 2,15,null,13 FROM dual UNION ALL
SELECT 4,20,null,20 FROM dual UNION ALL
SELECT 3,7,null,18 FROM dual
)
---------------------------
-- sample data above
---------------------------
SELECT *
FROM t
MODEL
dimension BY ( i )
measures (col1,col2,col3)
rules
(
col2[i>1] = col2[cv()-1] - col3[cv()-1]
)
ORDER BY i
;
ORA-32637: Self cyclic rule in sequential order MODEL
The ORA error is self explanatory and so I guess problem is using
col2[i>1] = col2[cv()-1]
The solution I want to achieve is -
col2 = (col2(previous row) - col3(previous row)) + col1(current row)
So, my expected output should be -
1 10 10 8
2 15 17 13
3 20 24 20
4 7 11 18
and so on...
Please let me know if this can be achieved using Model Clause.
Any response in this regards is much appreciated.
Thanks and Regards,
-- Ranit
(on Oracle 11.2.0.3.0)