Elegant solution, Grizzly, but it doesn't satisfy the OP's requirement
to allow the user to roll up into Michigan or down into Mississippi.
Trevor Briggs
Analyst/Programmer
Lincare, Inc.
(727) 431-1246
TBriggs2@xxxxxxxxxxx
-----Original Message-----
From: web400-bounces@xxxxxxxxxxxx [mailto:web400-bounces@xxxxxxxxxxxx]
On Behalf Of Grizzly Malchow
Sent: Thursday, December 12, 2013 1:07 PM
To: Web Enabling the IBM i (AS/400 and iSeries)
Subject: Re: [WEB400] "Position To:" Design Challenge with SQL
Paul,
I believe you are looking for a solution to handle pagination.
Here's what I use. It's a 2 step process intended for DB2.
First step is to get the total number of records (This step is
optional):
SELECT COUNT(STATE) FROM US_PEOPLE
WHERE STATE='MN'
Then I run something along the following lines to select a page of data
(Assume each page will have 12 rows at the most):
SELECT * FROM
(SELECT US_PEOPLE.*,rownumber() OVER(ORDER BY STATE, LASTNAME) AS
ROW_NEXT FROM US_PEOPLE
WHERE STATE='MN'
) AS PEOPLE_TEMP
WHERE ROW_NEXT BETWEEN 1 AND 12
In the above statement I am selecting rows 1 - 12 from the result. The
function rownumber() returns the unique number for the row in the result
set. Since it is followed by OVER(ORDER BY STATE,LASTNAME), the rows
will be sequential to the sorting options.
If someone were to navigate to the next page you would just add 12 to
the start and end range. So page 2 would be rows 13 through 24.
Since I have the total number of records, I can easily find out how many
pages there are dividing the total by the number of rows you want per
page. This makes it possible to show the user what page they are on if
that is desired. You will want to make sure to accommodate for the last
page not having 12 rows. For example if you have 18 total records you
will really have 2 pages, but the 2nd page would only have 6 rows.
You can create SQL Stored Procedures so that it can be used by RPG, or
any other language that supports calling SQL Stored Procedures.
I can't say if there is a better solution performance wise, but this
method is acceptable for me.
-----Original Message-----
From: web400-bounces@xxxxxxxxxxxx [mailto:web400-bounces@xxxxxxxxxxxx]
On Behalf Of Holm, Paul
Sent: Thursday, December 12, 2013 11:24 AM
To: web400@xxxxxxxxxxxx
Subject: [WEB400] "Position To:" Design Challenge with SQL
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.
midrange.com
