Re: "Position To:" Design Challenge with SQL

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.