 |
|
|
| > |
QUESTION POSED ON: 01 November 2004
I have a select query that returns 1000 rows and I want to display the rows from 200 to 500. Could you please let me how to code after the where clause? I know how to get the first 100 rows using -- FETCH FIRST 100 rows only clause.
|
|
|
To continue reading for free, register below or login
To read more you must become a member of SearchDataManagement.com
If you are using DB2 V7 or higher, consider using scrollable cursors. With scrollable cursors, you can move directly to the rows you want without having to FETCH every other row returned by the cursor.
To use scrollable cursors you must use declared temporary tables, another new feature of DB2 Version 7. (Unless you are using DB2 V8 where dynamic scrollable cursors are available.) DB2 uses a declared temporary table to hold and maintain the data returned by a scrollable cursor. Scrollable cursors allow developers to move through the results of a query in multiple ways. The following key words are supported when fetching data from a scrollable cursor:
NEXT - will FETCH the next row, the same way that the pre-V7
FETCH statement functioned
PRIOR - will FETCH the previous row
FIRST - will FETCH the first row in the results set
LAST - will FETCH the last row in the results set
CURRENT - will re-FETCH the current row from the result set
BEFORE - positions the cursor before the first row of the results set
AFTER - positions the cursor after the last row of the results set
ABSOLUTE n - will FETCH the row that is n rows away from the first row in the results set
RELATIVE n - will FETCH the row that is n rows away from the last row fetched
For both ABSOLUTE and RELATIVE, the number n must be an integer. It can be either a positive or a negative number and it can be represented as a numeric constant or as a host variable. All of the FETCH options for
scrollable cursors also reposition the cursor before fetching the data.
For example, let's consider your problem of a cursor that returns 1000 rows, but you only want rows 200 through 500. Consider the following cursor logic:
DECLARE csr1 SENSITIVE STATIC SCROLL CURSOR
FOR SELECT FIRSTNAME, LASTNME
FROM DSN8710.EMP
ORDER BY LASTNME
FETCH FIRST 1000 ROWS ONLY;
OPEN csr1;
FETCH ABSOLUTE 200 csr1 INTO :FN, :LN;
I used the FETCH FIRST 1000 ROWS ONLY clause to ensure that no more than 1,000 rows were returned. This clause is, of course, optional. Then I open the cursor and FETCH row 200. This positions the cursor just after the 200 result row that was just fetched. After that, all you would need would be to create a loop that just issues FETCH NEXT 300 times and that would retrieve only rows 200 through 500.
Basically, scrollable cursors reduce the amount of time and effort required to move backward and forward through the results of SQL queries. But as helpful as scrollable cursors are, do not make every cursor a scrollable cursor. Scrollable cursors require substantially more overhead than a traditional, non-scrollable cursor. Analyze the requirements of your applications and deploy scrollable cursors only where it makes sense to do so. Also, be sure to discuss this with your DBAs before implementing as there will probably be some setup work required of the DBA group to facilitate this solution.
Good luck...
Editor's note: Do you agree with this expert's response? If you have more to share, post it in one of our discussion forums.
|
|
|
|
|
|
|
|
Search and Browse the Expert Answer Center
Search and browse more than 25,000 question and
answer pairs from more than 250 TechTarget industry experts.
|
|
|
|
|
|
|
|
|
|
|
|
