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

If they all have the same physical files and keys then no only one index logical just becomes a format index is shared.
Sent from my Verizon Wireless 4GLTE smartphone

----- Reply message -----
From: "Alan" <cfuture@xxxxxxxxxxx>
To: "RPG programming on the IBM i / System i" <rpg400-l@xxxxxxxxxxxx>
Subject: Reduce large amount of logicals in SUBFL pgm, take in another direction
Date: Sun, Jul 31, 2011 3:07 pm


It seems to me with 80 logicals, if you're doing any updating to the
physical file(s), it wll take a good chunk of milliseconds/seconds to
update them all for updates...

If you have logicals that are duplicates of each other, only use one and
research whether you can delete it (after going thru programs that use it).

aec


On 7/29/2011 2:57 PM, Sharon Strippoli wrote:

Hi Michael, I just tried that and it does go faster than a straight
statement. This will be useful when this pgm goes against our history file,
which has much more data.

Plus it is very concise. I just needed to be sure what it was doing.
Thanks for the explanation. This helps a lot.

Sharon




Sharon Strippoli
Pilot Freight Services
IT Dept.
Phone (610) 891-8113
sharonstrippoli@xxxxxxxxxxxxxxxxx
http://www.pilotdelivers.com




On Fri, Jul 29, 2011 at 2:34 PM, Schutte, Michael D<
Michael_Schutte@xxxxxxxxxxxx> wrote:

Sharon, I use this method a lot.

Where :selectedValue in (' ', fld1)
And :selectedValue2 in (0, fld2)

Basically it's saying if selectedValue is blank then return all records
(your forcing the statement to always be true ' ' = ' '). However, if
populated, then only return records where fld1 equals selected value. Then
the same thing with selected value2. If zero then return all records,
otherwise where fld2 matches selectedvalue2. I firmly believe that it
performs better than using OR.

Try a sample statement in STRSQL.

Select * from unitmaster a
Where :region in (0, a.region)

Replace region any number and see the results.


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Sharon Strippoli
Sent: Friday, July 29, 2011 12:54 PM
To: RPG programming on the IBM i / System i
Subject: Re: Reduce large amount of logicals in SUBFL pgm, take in another
direction

Hi Charles, can you give me more insight in this part of code you sent:

where :selectedValue in (' ', fld1);
order by
case when :selectedValue = ' ' then myfield else fld1;

Selected value would be on my field value and in the parens you have a
blank, then a fld1

If I have multiple fields to go against how would that work? Would I build
the selected value in the way you don't suggest? Haven't done SQL in RPG
for a while.

I could do the case statement in my where part of the SQL statement, that
would account for as many fields as I want I would think.

And also thanks for your input.




Sharon Strippoli
Pilot Freight Services
IT Dept.
Phone (610) 891-8113
sharonstrippoli@xxxxxxxxxxxxxxxxx
http://www.pilotdelivers.com




On Fri, Jul 29, 2011 at 12:35 PM, Charles Wilt<charles.wilt@xxxxxxxxx

wrote:
Dynamic SQL is a possible solution...but if you use it, make sure
you're doing it with parameters

wSqlStmt = 'select myfield from mytable where fld1 = ?';

exec SQL
prepare S1 from wSqlStmt;
exec SQL
open C1 using :selectedValue;

and not this:
wSqlStmt = 'select myfield from mytable where fld1 = ' + selectedValue;
exec SQL
prepare S1 from wSqlStmt;
exec SQL
open C1;

which is open to SQL injection.

Dynamic SQL is not usually required however; static SQL can handle
most variable WHERE and ORDER BYs and usually performs better...

exec SQL
select myfield from mytbale
where :selectedValue in (' ', fld1);
order by
case when :selectedValue = ' ' then myfield else fld1;




On Fri, Jul 29, 2011 at 11:33 AM, Monnier, Gary<Gary.Monnier@xxxxxxxxx>
wrote:

As others have said it sounds like Dynamic SQL is the way you want to
go.

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

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

________________________________

Notice from Bob Evans Farms, Inc: This e-mail message, including any
attachments, may contain confidential information that is intended only for
the person or entity to which it is addressed. Any unauthorized review, use,
disclosure or distribution is strictly prohibited. If you are not the
intended recipient, please contact the sender by reply e-mail and destroy
all copies of the original message and any attachments.
--
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.