AW: Even more embedded SQL.... -- RPG400-L

Hi guys,

after everybody answered, just an other idea.
I used it for a single page subfile with a very large table. But I have to
admit, I didn't try it for dynamic sorts.

I declare 2 cursors (the first one for pageing from the first page and the
other one for pageing backward from the last row)
The SQL-Statements I use looks as follows:
1.  With Forward as (Select Field1, Field2, ... FieldN
                       from MyTable
                       where ....
                       Order By  Key1, Key2, Key3
                       Fetch First :X Rows only),
         DetRow as (Select * from Forward
                       Order By Key1 desc, Key2 desc, Key3 desc
                       Fetch first :Y Rows only)
    Select * from DetRow
       Order By Key1, Key2, Key3

Let's assume, my subfile has 10 rows per page and I want to show the third
page, I have to set X to 30 and Y to 10. For the next page I only have to
add 10 to X.
In a multiple row fetch I fill an array data structure and from this I'll
fill my subfile page.

For the backward paging only DESC and ASC will be reversed in the SQL
statement.

The only problem might be positioning relative on a key (not a relative row
no), and pageing forward and backward (as soon as the first or last row is
reached that is before of after the key).
In my program I had additional different selection criteria the user could
enter.
Normally a user will not page through 100s of subfile pages.

As I said, I didn't try it with different sorts, but it should even work
with static SQL and using CASE-Clauses for the order bys.

Mit freundlichen Gru?en / Best regards

Birgitta

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les Brown)

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Alan Shore
Gesendet: Donnerstag, 3. August 2006 16:21
An: RPG programming on the AS400 / iSeries
Betreff: Re: Even more embedded SQL....



Johnny - thanks for your reply, but I am betting that you do a load all
sub-file, and NOT a page at a time sub-file, which is what I need for
15,000 or more records.

I'm pretty sure that what I need to load page at a time is
FIND NEXT
FIND CURRENT
FIND RELATIVE
for things as Page up, Page Down etc.
but I'm not too sure how to do a position to within the sub-file using SQL


Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx




             Johnny Johnson
             <jjohnson@hubgrou
             p.com>                                                     To
             Sent by:                  RPG programming on the AS400 /
             rpg400-l-bounces@         iSeries <rpg400-l@xxxxxxxxxxxx>
             midrange.com                                               cc

                                                                   Subject
             08/03/2006 10:08          Re: Even more embedded SQL....
             AM


             Please respond to
              RPG programming
              on the AS400 /
                  iSeries
             <rpg400-l@midrang
                  e.com>






I've always just built my sql statement based on what column(s) the user
selects to sort and then just filled the subfile sorted the way the sql
returns the data.
I'm not sure if this is what you are thinking, but it seems to work really
well.

Johnny Johnson
Hub Group, Inc.




             Alan Shore
             <AlanShore@xxxxxx
             om>                                                        To
             Sent by:                  RPG programming on the AS400 /
             rpg400-l-bounces@         iSeries <rpg400-l@xxxxxxxxxxxx>
             midrange.com                                               cc
                                       RPG programming on the AS400 /
                                       iSeries <rpg400-l@xxxxxxxxxxxx>,
             08/03/2006 08:53          rpg400-l-bounces@xxxxxxxxxxxx
             AM                                                    Subject
                                       Even more embedded SQL....

             Please respond to
              RPG programming
              on the AS400 /
                  iSeries
             <rpg400-l@midrang
                  e.com>












  Hi All,

  I have been given a project that requires the need to display, within a
subfile, the data from 2
  separate files with the ability to sort that data from any number of
different columns. So I
  thought to myself, this smacks of embedded SQL and Dynamic Sorting
Subfiles. The unfortunate
  thing is that the data I have to display is over 15,000 records long (and
growing) so if anyone
  can help me, I am looking for an example of embedded SQL and Dynamic
Sorting Subfiles - Page at
  a time (with the ability to position to a particular piece of data)"

  Some title for an article huh? If anyone has an example of what is
required, or could point me
  in some direction, I would be very grateful.










Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.



--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.