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