How to SELECT TOP 100 and then (if i choose) the following 100 until end of rows?sql server

[ahmed_tawfik]

Hi

in powerbuilder app in open event of window that contains dw_1 script containe dw_1.retrieve()
as i have thousands of rows then in dw_1 in sql syntax i added top 100 after select select top 100 col_name from tab_name
thus i retrieve only 100 rows from table
now i want to put buttons on the window (next-prev) when user clicked it he retrieve next/prev 100 rows (according to rows already retrieved) and when it is last/first row next/prev is disabled
also when i try to search and pass arg to dw (dw.retrieve(arg)) as pb search in 100 row that already displayed i want when to begin search pb search on all rows (ie clear top 100 in the select stat,) and return only 10 rows that satisfy what i search displaying button (next/prev) to show following rows

Best Regards

[phil_soady]

It is possible since 7.52

https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abapselect_up_to_offset.htm

This is a common requirement and makes perfect sense.

[Former Member]

use of 'next X' gets slower over large number of records since it has to count them.

use value based limits instead of row limits to make it fast. something like:

select top 100 mycolumn
from mytable
WHERE mycolumn > :ll_lastretrievedid
order by mycolumn

[ahmed_tawfik]

Hi

thank for every one replied to me

Tóth Péte replied "we use informix: select skip 10 first 10 * from table order by …" i found similar in sql server 2012 that is "

OFFSET N ROWS FETCH NEXT X ROWS ONLY

In SQL server 2008 i can use

ROW_NUMBER() OVER( as replied from Gökalp AKYÜZ) but offset and fetch is more easier

as Roland Smith suggested to alter sql synatx in sqlpreview event of DW i wrote thes code in the sqlpreview event

ls_sql = dw_1.GetSQLSelect()

SqlSyntax = ls_sql + ' ORDER BY import_letter.im_letter_serial DESC, import_letter.im_letter_no DESC OFFSET ' + string(ii_offset) + ' ROWS FETCH NEXT 10 ROWS ONLY'

setsqlpreview(SqlSyntax)

where ii_offset is instance var -its initial val = 0 and when i use next page ii_offset = ii_offset + 10

it works fine but also there are noticeable delay time (may be as i try on server with 8 GB ram only)

Best Regrds

[Former Member]

You should try it in a multi user setup and see. If your datawindow is readonly (no updates) then you should be ok and instead of specifying a sort expression, user ORDER BY in your query.

You can also create views on the database to prevent locking but that will be more involved.

[ahmed_tawfik]

Retrieving rows as needed may had following problems

In a multiuser situation, Retrieve Rows As Needed might lock other people out of the tables.

and

Retrieve Rows As Needed is overridden if you have specified sorting

[phil_soady]

What ???

Why would using the SQL READ that uses skip soundly introduce lock problems ?

We are talking about sql READ , requesting to read LESS data than a normal SQL select statment.

There is no talk of update here. On which DB platofmr does SAP DB interface convert selects to selects with hold/lock?

Indeed by default SAP does uncommited reads on the DB !!!.

The SQL options

Take 5 (up to n rows in abap)

skip 10 (in abap offset off)

make perfect sense and are used extensively outside of SAP. Especially when combined with order by.

Please share on which platform you saw ABAP select up to x rows cause a db lock.

Since 7.52 it is indeed possible in ABap , to do select x rows skip y rows.

https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abapselect_up_to_offset.htm

[Former Member]

Retrieving rows as needed

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00844.1250/html/pbug/CAIB...