How to mimic SELECT ... LIMIT, OFFSET in OpenEdge SQL?
openedge, progress-4gl
Solution
OpenEdge 11.2 added support for `OFFSET` and `FETCH` clauses to SQL `SELECT` queries; versions of OpenEdge below 11.2 do not support `OFFSET`/`FETCH`.
From the 11.2 product documentation "SQL Reference" document:
The OFFSET clause specifies the number of rows to skip, before starting to return rows
from the query expression. The FETCH clause specifies the number of rows to return,
after processing the OFFSET clause.
It's worth noting that the `TOP` and `OFFSET`/`FETCH` clauses are mutually exclusive - `TOP` cannot be used in a query that uses `OFFSET` or `FETCH`.
Example queries from the documentation:
Skip the first 10 rows and return the rest of the qualified rows:
SELECT OrderID,OrderDate,custID,filler
FROM dbo.Orders OFFSET 10;
Return the first 10 rows without skipping any:
SELECT OrderID,OrderDate,custID,filler
FROM dbo.Orders
ORDER BY OrderDate DESC, OrderID DESC
FETCH FIRST 10 ROWS ONLY;
Return rows 51 through 60 in the result set of the query:
SELECT OrderID,OrderDate,custID,filler
FROM dbo.Orders
ORDER BY OrderDate DESC, OrderID DESC
OFFSET 50 ROWS FETCH NEXT 10 ROWS ONLY;
Problem
It's a common thing in most SQL implementations to be able to select a "sliding window" subset of all the rows returned in a query. A common use case for this is pagination. For example, say I have a search page with 10 results on each page. For implementations that support `LIMIT` and `OFFSET` keywords, the query used to return results for each page would be as follows: page one would use `SELECT ... LIMIT 10 OFFSET 0`, page 2 would use `SELECT ... LIMIT 10 OFFSET 10`, page 3 would use `SELECT ... LIMIT 10 OFFSET 20`, etc. (note that the `OFFSET` takes effect before the `LIMIT`). Anyway, I'm trying to mimic this functionality in OpenEdge's SQL engine. I've already figured out that `SELECT TOP` is basically equivalent to `LIMIT`, however I can't find anything similar to `OFFSET` (I don't think there is an exact equivalent). SQL Server and Oracle also lack an `OFFSET`, but they have a pseudocolumn called `ROWCOUNT` and `ROWNUM`, respectively, that can be used to mimic the behavior using nested selects (see here and here). In the 10.2B SQL Reference doc, p49 there is a subsection entitled TOP clause that says at the bottom `SELECT TOP` is the functional equivalent of the Oracle `ROWNUM` functionality. Note that `SELECT TOP` is defined simply in terms of a limit on the result set size, and the optimizer determines how to use this limit for best data access. Thus, `SELECT TOP` does not have all the "procedural rules" used to define the meaning of the Oracle `ROWNUM` phrase. However, this seems to be inaccurate as according to `TOP`'s syntax it cannot be used as a predicate like `ROWNUM` can (e.g. I can't say `SELECT * FROM Customer WHERE TOP > 5 AND TOP < 10`). So `TOP` is not functionally equivalent to `ROWNUM`. Is there any way to mimic `OFFSET`, or am I out of luck?