I have the following question. Is it possible to create a user-defined analytical function? If so, can you post a link that can guide me into it. I'm using Oracle 10.1g.
I am aware that one can create a user-defined aggregate function, and then use it as analytical, but there is a limitation that the aggregate function can have only 1 argument. My function needs to have more (like LEAD for example).
The resulting function would look like this:
select MIN_COMPARATIVE(sequencenr, sequencenr) OVER (ORDER BY CR_DATE, sequencenr ROWS BETWEEN 100 PRECEDING and 100 FOLLOWING) from TableA
This would give me the minimum sequencenr in the window of 100 preceding and 100 following records, which is bigger than the sequencenr in the current row. If you have any other idea how this can be achieved, it would be much appreciated.