Need help writing a SQL statement (To work with Cursor State)

[Guest_imported]

Hi all,

I would like to write a SQL statement that would allow me to select 50 records at a time from a table. I had two ideas on how I would like to do this. One would be to use the < > keys and on the push of the button it would go through 50 records , then the next 50 and so on. Or I'd would like to allow my user to enter a row to start on and then select the next 50 from that point. I am not sure even where to start looking. Does anyone have any sample script for it or ideas?

 

[rac2]

You might try the ASP forum, search on paging. Most of your solution will depend on how you are developing the application and on the particular database system, not much on the SQL query.

 

[Guest_imported]

Thanks for the prompt reply. I know in Oracle I could use ROWNUM it is proprietory to oracle and go to the row number you specify. I was hoping that SQL Server 2000 or DB2 had a similar function.

If anyone can think of a way to do this for either of those Databases, I would be most gratefull. I will post my question to the asp forum, I am using .NET as the front end.

- DB2MAN

 

[gregsimpson]

This can be accomplished quite easily if you are using Version 7 of DB2. Version 7 allows &quot;scrollable&quot; cursors.

DB2 Version 7 allows for ABSOLUTE cursor positioning. This will effectively move the cursor to an absolute position (a specific row relative to the top or bottom of the answer set depending on the plus or minus number). The absolute position can be specified by a constant or a host variable

It is also now possible to position the cursor using predicates such as FIRST,LAST,NEXT,PRIOR,AFTER,BEFORE and CURRENT.

The CURSOR can be defined as SENSITIVE or INSENSITIVE. If it is SENSITIVE it picks up DELETES and UPDATES(but not inserts) on the table you have declared the CURSOR against. If it is insensitive it works by creating a result set in a Temporary Global Table.

Scrollable Cursors use Temporary Global tables in the background so as a pre-requisite you must have a Tablespace/permission in your database for creating these.

 

[tlbroadbent]

Check the following for SQL Server solutions:

&quot;Paging through Records using a Stored Procedure&quot;

http://www.aspfaqs.com/webtech/062899-1.shtml.

Paging: Use ADO, getrows, or a Stored Procedure?

http://www.15seconds.com/issue/010308.htm

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.