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

Sharon,

The process that builds your SQL statement can have parameters passed to
it. One of these parameters can be a list of selections. Something
like this.

pgmToBuildSQL PR
nbrFieldsForWhereClause
arrayOfFieldsForWhereClause
arrayOfValuesForWhereClause


SQLStringBegin = 'Select * From yourfile ';

X = 1;
startPos = 1;

doW (X < = nbrFieldsForWhereClause);

whereValue = arrayOfFieldsForWhereClause(X) +
arrayOfValuesForWhereClause(X); //ValueFor can be = value, like value,
etc
valueLen = %len(%trimr(whereValue));
%subst(whereClause:startPos:valueLen) = whereValue;
startpos = startPos + valueLen + 1;
X = X + 1;

enddo;

Return;

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Sharon Strippoli
Sent: Friday, July 29, 2011 9:54 AM
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.