You're right. My apologies.
Seems to me the OP could just create a SQL stored procedure or UDTF that calls an RPG program then. The RPG program could then do all of the file positioning and building of results.
Any other purely SQL solution I could offer would be along the same lines as others have answered with.
-----Original Message-----
From: web400-bounces@xxxxxxxxxxxx [mailto:web400-bounces@xxxxxxxxxxxx] On Behalf Of Briggs, Trevor (TBriggs2)
Sent: Thursday, December 12, 2013 12:11 PM
To: Web Enabling the IBM i (AS/400 and iSeries)
Subject: Re: [WEB400] "Position To:" Design Challenge with SQL
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.
--
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.
************************************************************************************************************************************************************************************************************
This message originates from Lincare Holdings Inc. It contains information which may be confidential or privileged and is intended only for the individual or entity named above.
It is prohibited for anyone else to disclose, copy, distribute or use the contents of this message.
All personal messages express views solely of the sender, which are not to be attributed to Lincare Holdings Inc., and may not be copied or distributed without this disclaimer.
If you received this message in error, please notify us immediately at MailAdmin@xxxxxxxxxxx or (800) 284-2006.
************************************************************************************************************************************************************************************************************
--
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
