|
Sharon,
As others have said it sounds like Dynamic SQL is the way you want to
go. What I've done in the past in similar situations is have the user
specify their sort sequence. This way you only have to concern yourself
with a default sequence if one isn't supplied. The default may be no
specified sequence at all or the most used sequence.
It also sounds like the program you are grappling with has reached a
point where some modularity will be beneficial. At the very least you
can make editing and SQL string building separate modules - bound
module, sub-procedures, service program(s) take your pick.
Gary Monnier
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Sharon Strippoli
Sent: Friday, July 29, 2011 8:03 AM
To: RPG programming on the IBM i / System i
Subject: Re: Reduce large amount of logicals in SUBFL pgm,take in
another direction
Thanks for the input everyone,
while doing my research through this program someone who didn't
understand logicals made a bunch of keys at the top of the pgm that list
3 fields then the next key has those same 3 plus another field. I
haven't checked the section of code in detail where these are used, but
I assume if there are keys set up like that someone made logicals that
match, instead of making one with 6 possible hits and also use it when
you only have the first 2 or 3 fields. So I figure I can get rid of
lots of logicals, but not sure yet.
My Manager has researched the usage by user session and some folks input
lots of data to search against. We have discussed grouping some fields
so a query would have say at least 4 or 5 pieces of data to go against,
and could group query builds upon this predetermined set of fields. And
then educate the users to scroll up or down to find their record, if it
was not a direct hit.
But when thinking of how to set up the SQL statement, is there a
streamline way to set it up? I need to check if input fields are
populated and organize how to build based on how many pieces of info I
have to select against. I am looking at how many groups of logicals I
have based on repeating key fields.
Sharon Strippoli
Pilot Freight Services
IT Dept.
Phone (610) 891-8113
sharonstrippoli@xxxxxxxxxxxxxxxxx
<mailto:sharonstrippoli@xxxxxxxxxxxxxxxxx>
http://www.pilotdelivers.com <http://www.pilotdelivers.com>
On Fri, Jul 29, 2011 at 9:55 AM, Morgan, Paul <Paul.Morgan@xxxxxxxxxxx
<mailto:Paul.Morgan@xxxxxxxxxxx> >wrote:
Sharon,
Use SQL as others have recommended but you might not be able to get
rid of all those logicals. SQL would still need them depending on
your selection and sorting.
I'd make sure all those logicals don't do any select/omits so SQL can
take full advantage of them.
See if some of those logicals are redundant. If you have a logical
keyed on fielda and a logical keyed on fielda and fieldb you could
delete the redundant logical with the shorter key.
If you are doing some sort of summarization and your data doesn't
change that much you could pre load a work file with the summaries
then run your queries against the summarized work file instead of thefull data set.
Do they really select on all those fields and other criteria? Do they
really select on some fields then refine their selection on other
fields and criteria? I'd think about adding some logging to see what
selection and other criteria is actually being used and how they'drill down'.
Paul Morgan
Principal Programmer Analyst
IT Supply Chain/Replenishment
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx<mailto:rpg400-l-bounces@xxxxxxxxxxxx>
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]<mailto:[mailto:rpg400-l-bounces@xxxxxxxxxxxx]>
On Behalf Of Sharon Strippoli
Sent: Friday, July 29, 2011 8:11 AM
To: rpg400-l@xxxxxxxxxxxx <mailto:rpg400-l@xxxxxxxxxxxx>
Subject: Reduce large amount of logicals in SUBFL pgm,take in another
direction
Good morning,
new job and given the assignment to revamp a screen pgm that the whole
company uses to make it more efficient, less CPU.
There are 12 different fields a user can fill in to search for the
info they want. Plus there are fields they can check off for other
criteria. Total about 25 different fields on the screen.
Currently, there are about 80 logicals that are used for all the
different combinations of fields to search on. The first SUBR of the
pgm does various validations against all the possible fields, then
depending on that result does a SETLL for the appropriate logical.
Then a second SUBR checks the fields again and depending on that does
a READ, READE or READP against the logical that was staged.
They want to select the data the first time through to a type of work
file, then as more information is filled in by the user, query or read
against that work file, drilling down further for each field that is
populated by the user. They want to reduce CPU and the amount oflogicals.
I was thinking of doing a query against the work file as it gets
drilled down.
Wondering how the super users that use this forum would take this on.
Any input would be appreciated.
Thanks.
Sharon
Sharon Strippoli
Pilot Freight Services
IT Dept.
Phone (610) 891-8113
sharonstrippoli@xxxxxxxxxxxxxxxxx<mailto:sharonstrippoli@xxxxxxxxxxxxxxxxx>
http://www.pilotdelivers.com <http://www.pilotdelivers.com>
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx<mailto:RPG400-L@xxxxxxxxxxxx> To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l<http://lists.midrange.com/mailman/listinfo/rpg400-l>
or email: RPG400-L-request@xxxxxxxxxxxx<mailto:RPG400-L-request@xxxxxxxxxxxx> Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l<http://archive.midrange.com/rpg400-l> .
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx<mailto:RPG400-L@xxxxxxxxxxxx> To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l<http://lists.midrange.com/mailman/listinfo/rpg400-l>
or email: RPG400-L-request@xxxxxxxxxxxx<mailto:RPG400-L-request@xxxxxxxxxxxx> Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l<http://archive.midrange.com/rpg400-l> .
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx
<mailto:RPG400-L@xxxxxxxxxxxx> To subscribe, unsubscribe, or change
list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
<http://lists.midrange.com/mailman/listinfo/rpg400-l>
or email: RPG400-L-request@xxxxxxxxxxxx
<mailto:RPG400-L-request@xxxxxxxxxxxx>
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l
<http://archive.midrange.com/rpg400-l> .
--
This is the RPG programming on the IBM i / System i (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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
