RE: Reduce large amount of logicals in SUBFL pgm, take in another direction

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 the

full 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 of

logicals.

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> .