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
