Kbase P13258: Is the SELECT TOP option supported in Progress SQL-92?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
27/09/2007 |
|
Status: Verified
GOAL:
Is the SELECT TOP Option supported in Progress SQL-92?
GOAL:
Is the rownum option supported in Progress SQL-92?
FIX:
Yes, the TOP (or rownum) option is supported, having been added to the product in OpenEdge 10.1B SQL-92.
Whilst the TOP option is not available in earlier versions of the product but there are different ways to work around this limitation:
- By running query with WHERE clause in order to reduce the result set:
For example:
SELECT * FROM Table WHERE Field > 0 AND Field < 100
SELECT * FROM Table WHERE Field > 100 AND Field < 200
- With JDBC application, it can be worked around by controlling the fetch calls. Here is the basic scenario:
1. When the first fetch call (resultSet.next() method) from the Java/JDBC application is sent to the server and executed,
the server will retrieve the number of rows based on the fetch size(by default is 50) if there are and sent them back to the JDBC driver.
2. Then every next fetch call from the application will only read each row from the result set on the driver side until the end while no server action involved.
3. If there are more fetch calls beyond the fetch size, then another fetch call will be sent to the server and step#1 and #2 will be repeated.
One exception here regarding the table reading activity on the server side is that if the query does have either an ORDER BY or GROUP BY clause.
Then the entire table must be read.