Re: "Position To:" Design Challenge with SQL

[Steve Richter <stephenrichter@xxxxxxxxx>]

to get the next state you can use "fetch first row only" to get the state
code from the US_PEOPLE file:

exec sql
select   a.state
into      :vState
from      us_people a
where   a.state > :currentState
order by a.state
fetch first row only ;

and the prior state:
exec sql
select     a.state
into       :vState
from      us_people a
where   a.state < :currentState
order by    a.state
fetch first row only ;

then once you have the state to display use a cursor to read and write each
row for that state.

exec sql
declare c1 cursor for
select   a.state, a.lastname
from    us_people a
where   a.state = :currentState
order by    a.lastname ;

exec sql
open   c1 ;

dow   1 = 1 ;
exec sql
fetch   c1
into    :vState, :vLastName ;
if     sqlcode <> 0 ;
leave ;
endif ;

enddo ;

exec sql
close   c1 ;


-Steve



On Thu, Dec 12, 2013 at 12:24 PM, Holm, Paul <pholm@xxxxxxxxxxxxxxxxx>wrote:

> All,
>
> I'm interested if anyone has worked on a design to enable "position to"
> type of web searches.  Any insight or gotchas would be appreciated.  We
> have a series of requirements that require a web search but with the
> ability to scroll up or down from that key value position.  I know RPG does
> this but we need an SQL based solution.
>
> To further explain the need...Assume you have a database table of all
> people living in the US.  We want to be able to search based on a number of
> criteria such as "state".  So I search for "MINNESOTA" and I'm presented
> with a list/subfile of people living in MN.  Example SQL:  SELECT * FROM
> US_PEOPLE where STATE = "MN"  order by STATE, LASTNAME
>
> Now they want to "scroll up" in the list which should then display people
> in "MICHIGAN" .  The issue is "MICHIGAN" is not in my result set.  If I
> scrolled down thru MN then I would see "Mississippi".
>
> We are dealing with large tables so we need to make the searches perform.
>  We also obviously are looking for a "generic" design so it can be used for
> numerous inquiry applications...   Anyone have experience?
>
> Thanks in advance, Paul Holm
>
>
> www.planetjavainc.com
> --
> This is the Web Enabling the IBM i (AS/400 and iSeries) (WEB400) mailing
> list
> To post a message email: WEB400@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/web400
> or email: WEB400-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/web400.