How I miss ROWNUM sometimes...

Today I needed to calculate the average from a field from a table in Intersystem's Caché. The logic was very simple: I just neede the average of prices of last N purchases, after some date.

Oracle has the pseudo-column ROWNUM, which makes my problem very easy to be solved. I would have something like:

SELECT AVG(PRICE)
FROM PURCHASE
WHERE DATE > '01/01/01' AND ROWNUM <= 20
ORDER BY DATE DESC

In this simple example, we calculate the average of price of last 20 purchases made after day 01/01/01. Quite simple, no?

I searched for something similar in Caché, but I couldn't find it. I found some related stuff, like a variable for the number of lines a query resulted, or the TOP statement, used within SELECT. Unfortunatelly, nothing helped me much, so I had to open a cursor (or I could have used a ResultSet, if I wanted to), and iterate through the results, and calculate the average myself.

So, the query took this form:

SELECT TOP 20 PRICE
FROM PURCHASE
WHERE DATE > '01/01/01'
ORDER BY DATE DESC

See the 'TOP 20'. It that for the query I just want the first 20 records. Unfortunatelly, we can not use it on WHERE statement, so we can not use it for the AVG function.

I will not describe how I iterate through a cursor and calculate the average, since it's quite trivial, and I'm tired now.

Today, I'm done.