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).
However, in the OpenEdge 10.2B SQL Reference doc, p49 there is a subsection entitled TOP clause that says at the bottom:
SELECT TOPis the functional equivalent of the OracleROWNUMfunctionality. Note thatSELECT TOPis 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 TOPdoes not have all the "procedural rules" used to define the meaning of the OracleROWNUMphrase.
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?
Select the top N in some known order, save the key of the last one, and select top N where greater than that key.
Lather, rinse, repeat.
That works when results are sorted in order of PK, but that's not always going to be the case. Also, I would like to keep the query as state-agnostic as possible, if I can.
Or any other key by which you choose to order the results ...
Bottom line, you can't do it the way you want because it isn't there, so you have to figure out what works.
If you want real control, then use ABL!
BTW, that isn't as facitious as it might sound since it is very workable to do non-ABL client, e.g., reporting working with an AppServer and ABL code to select the data.
Thanks, I appreciate the responses with the ideas, even if the end result is "it can't be done."
I'm wrapping the JDBC driver directly so relying on the application code to do the right thing or have the correct structure doesn't fit into my requirements. Guess I'll just have to manually nudge the ResultSet... this won't be pretty, haha.
Does the site have AppServer? If so, consider Open Client.
My code is written in anti-ABL, so if it comes into contact with ABL (even nearly, including on the AppServer), the two will annihilate eachother and my server!
Regardless of attitude, the difference between a network connection to a DB vs a network connection to an AppServer is not much ... except that the AppServer one gives you a lot more control. Even without AppServer I have done complex reporting applications that involved ABL code which took in the parameters, processed and exported the data, then transferred control to a reporting function which drew data from the export.
Thanks, I wasn't trying to be haughty, just trying to avoid ABL in a comedic fashion. I shall take it into consideration.
One might ask, why try to avoid the most powerful and well-adapted language for interacting with an OE database?
Well, I'd rather not get too deeply involved in an explanation as it tends to turn into a religious war. But mostly because I don't think that ABL is a capable language for doing every task (there really isn't any language that is good at everything), and I feel like if I'm going to interact with OpenEdge data from another language, the lingua franca of database communication is SQL so that's what I'm going to use - because that's what all the existing bridge code is written in. I don't want to have to write ABL glue code every time I want to talk to an OpenEdge database, that's all.
Well, but in modern layered designs, it is fairly common for the language used in different layers to be different, especially that used for the UI. So, using ABL on AppServer as a data source is hardly "glue code", but rather just recognizing that there is a better way than SQL over a network to manage data access.
I will note in particular that Tom Bascom did a survey a while back looking for people who were doing meaningful amounts of *updating* or *creating* data via SQL and had a hard time finding them.
Well, but in modern layered designs, it is fairly common for the language used in different layers to be different, especially that used for the UI. So, using ABL on AppServer as a data source is hardly "glue code", but rather just recognizing that there is a better way than SQL over a network to manage data access.
You're entitled to your own opinion of course; my own is that ABL is a really longwinded language, especially considering how inflexible it is, so I make every effort I can to avoid it. If I absolutely had to do it your way, I would write as little ABL as possible, only using it on the AppServer and exposing it via Web interface, essentially making it into an API. I would write as much code as possible on the client-side in JavaScript, probably using backbone.js to model relations (which I can't give enough praise to; simply awesome library). I'd only use the Web server for validations and persistence
I will note in particular that Tom Bascom did a survey a while back looking for people who were doing meaningful amounts of *updating* or *creating* data via SQL and had a hard time finding them.
I can see why, as the SQL engine is lacking a bit of power compared to the ABL version. But it's not as bad as I originally thought, and it seems that looking at some knowledgebase articles there are some plans to improve it in later 11.x versions. I'm sure there weren't many people using OOABL when it came out either... someone has to give it a good thrashing to uncover the pain points.
Interesting that ABL manages to consistently be more productive, given that it is long winded and inflexible! I suppose assembler is the ultimate in flexability since one can do anything in it ... eventually.
Deciding to write in layers and what to put in layers isn't, of course, just a matter of individual style. What belongs in a layer belongs there because of what kind of responsibility it is, regardless of what language one chooses or is required to write it in.
With those attitudes, I can't imagine why you continue to work with OpenEdge at all. Particularly since you are electing to avoid the very thing, ABL, which gives OpenEdge its advantage.
Well, I don't continue to work in OpenEdge. I'm just writing a database adapter for a Ruby framework (using the free evaluation version of OE) so need to paper over some SQL issues - hence all the SQL questions. Also, this is why I didn't want to delve into the reasons too deeply... it just gets emotive and unproductive to answering my questions.
You can continue to think I'm performing a fool's errand (I'm sure most people on this board would agree), but I'm extremely stubbern so unlikely to waver until I've either succeeded or thoroughly crushed my head against the cement wall.
The sad part, of course, is that you are unlikely to run into any cement walls ... unless it is on purpose ... but that by doing things differently you could have made something significantly better.
Thanks for the vote of confidence! I think? lol
I think it is in the nature of programming that a reasonably clever programmer can *eventually* produce code which will do any reasonable task. Of course, it might have been accomplished 10X faster or run 10X more efficiently or have 10X more productivity or be 10X more suited to on-going evolution of requirements had it been done a different way, but it does work. It is when you assemble programmers into teams that the likelihood of out and out failure, budget and time overruns, and the like increases.
It's not possible in the current OpenEdge 11 and there doesn't seem to be a workaround that can be done from carefully crafted SQL as a general solution (not reliant on queries sorted on PKs, etc.). There is an enhancement request that has been logged to add a ROWNUM pseudo-column similar to what Oracle has. That request text verifies that one can only fetch all rows and manually adjust the ResultSet cursor afterwards:
... applications must retrieve the whole result set and implement the result set pagination programmatically on the client. This incurs both programming and performance costs.
I inquired about the status of the above enhancement request, and was just told that it is not planned for any upcoming release (my emphasis):
Hi Abe,Thank you for your email. This feature is not planned for any upcoming release at this time.Regards,Rob Holzel
So if the feature ever does make its way into the OpenEdge SQL engine, it probably won't be for a very long time yet.