× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I recommend trying with Visual Explain from Navigator.
Not sure what you want to do exactly, but maybe RRN() could help you.


El 23/03/2015 a las 12:20 p.m., Steve Richter escribió:
experimenting with using row_number to implement a SKIP type function.
Where an inner query use row_number to assign a sequential number to the
selected rows. And then the outer query says WHERE rownum >= 20 to skip
over the first 20 of the selected rows. The objective being to page thru
the rows of a table, either forward or backward.

Here is the code for paging forward:
exec sql
select a.seqnbr, a.rownum
into :vSeqnbr, :vRownum
from (

select a.seqnbr, row_number( )
over( order by a.seqnbr ) rownum
from dsh3526p a
where a.seqnbr > :inTopKey.seqnbr
order by a.seqnbr
fetch first 20 rows only ) a

where a.rownum >= :inSflPagSx
order by a.rownum
fetch first row only ;

The code works. And it performs well enough when there are up to 50,000
rows in the table. But the more rows in the table the slower the execution.

How can I use row_number in this manner efficiently? I tried the code
where I have a 3rd nested query which simply runs "fetch first 20 rows
only", returning those rows to the query which assigns the row_number which
returns its result rows to the outer query. Same poor performance.

thanks,


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.