RE: SQL Position-To with scroll-backwards capability

Ok, that's a little different.

So your criteria is "where FIRSTNAME like 'Dan%'"

Your results set would be
Dan
Daniel
<....>
Dann


>From the first page, you want to be able to do a position to: Dann

Then, be able to page up from there?


Can't be done.

First, you can't do the position to without closing the cursor and opening an 
new one, "where FIRSTNAME >= 'Dann'".

Once you open the cursor, you can't page backup past the first record returned. 
Like I said in my original post, you could fake this out by opening a new 
cursor, "where FIRSTNAME <= 'Dann' order by FIRSTNAME DESC"


The only possible way to do what you want to do would be to move the original 
10,000 record result set into a temporary storage area, either a table or user 
space.  This way, you could page back and forth and do whatever searching you 
need.

I wouldn't move all 10,000 at the beginning.  Just move them as you need to.



Again, subsetting instead of positioning is a better alternative when using SQL.


HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Dan
> Sent: Friday, July 29, 2005 3:09 PM
> To: RPG programming on the AS400 / iSeries
> Subject: Re: SQL Position-To with scroll-backwards capability
> 
> 
> On 7/29/05, Wilt, Charles <CWilt@xxxxxxxxxxxx> wrote:
> > First answer, can't be done.
> > When you say "where FIRSTNAME like 'Dan%', the cursor 
> begins at 'Dan', 
> > you can't got backwards past the beginning.
> 
> I think you misunderstand my question.  I want all 10,000 rows that
> meet the criteria (where FIRSTNAME like 'Dan%'), be able to position
> to 'Dann', *AND* then be able to scroll backwards from that point.
> 
> I am trying to avoid doing a PREPARE/OPEN everytime the user 
> wants to Page Up.
> 
> -- 
> This is the RPG programming on the AS400 / iSeries (RPG400-L) 
> mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
> 
>