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.