RE: Even more embedded SQL....

Thanks for your input Charles.
Right now, GUI is out of the question.
Your second point of "how realistic is it to allow the user to be able to
page
through 15,000 records". It is very realistic as each item in our item
master needs to be assigned a new Category/Sub-Category designation that is
NOT available at this time. The user realizes that they have their work cut
out for them, but they are insistent that this is what is needed for their
future sales analysis, but they have to designate this new field first.




Alan Shore

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




                                                                       
             "Wilt, Charles"                                           
             <CWilt@xxxxxxxxxx                                         
             om>                                                        To
             Sent by:                  "RPG programming on the AS400 / 
             rpg400-l-bounces@         iSeries" <rpg400-l@xxxxxxxxxxxx>
             midrange.com                                               cc
                                                                       
                                                                   Subject
             08/03/2006 11:16          RE: Even more embedded SQL....  
             AM                                                        
                                                                       
                                                                       
             Please respond to                                         
              RPG programming                                          
              on the AS400 /                                           
                  iSeries                                              
             <rpg400-l@midrang                                         
                  e.com>                                               
                                                                       
                                                                       




First off, this request is something that would be much, much easier
with the right GUI since the GUI separates the result set from the
presentation of that result set and usually by default includes the
capabilities to sort by any column.

Secondly, how realistic is it to allow the user to be able to page
through 15,000 records.  Is the user really going to hit page down
(15,000/20 =) 750 times???  Consider how you could subset the results to
a more reasonable amount.

There's basically two methods of dealing with this on the green screen.

1) Opening and closing cursors as needed prepared from a dynamic
statement (Johnny Johnson's method)
---But consider that the usual "position to" is replaced by "subset by"
or "start at".  The difference being with "position to" I can goto a
record in the middle and hit page up.  With "subset by" or "start at",
the first record on the new screen is the first record in the result
set, I can't hit page up.  You could try automatically closing the
current cursor and opening one that provides prior records. Position to
= MICHAEL means WHERE name >= 'MICHAEL', to hit page up, I'd need to
close the WHERE name >= 'MICHAEL' cursor and open one WHERE name <
'MICHAEL'.  Remember to handle the fact your reading backwards!

2) Emulate the GUI, separating the result set from the presentation.
Retrieve a single results from the DB and store it in memory (
RPG-array, userspace, ect) You'll need a way to search and sort this
result set in memory (bsearch & qsort).  This is going to be the most
flexible, including allowing for the normal "position to" or "subset
by".  But requires more work.

HTH,


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Thursday, August 03, 2006 9:53 AM
To: RPG programming on the AS400 / iSeries
Cc: RPG programming on the AS400 / iSeries;
rpg400-l-bounces@xxxxxxxxxxxx
Subject: Even more embedded SQL....












  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.