How to mimic SELECT … LIMIT, OFFSET in OpenEdge SQL? - Forum - OpenEdge General - Progress Community

How to mimic SELECT … LIMIT, OFFSET in OpenEdge SQL?

 Forum

How to mimic SELECT … LIMIT, OFFSET in OpenEdge SQL?

  • 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 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.

    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.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • 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.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • 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.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • 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.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • 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?

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • 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.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • 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.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • 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.