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

Sharon the details depend on the criteria, but you basically want to
make use of AND/OR and possibly CASE in your where in order to have
all your criteria included in a single statement...


--Example--
Select via:
1) Customer name
2) Customer State
3) Customer Class (some internal classification)

it's a little easier to understand in this format

select <...>
from <...>
where
(:selName = ' '
or :selName like custname )
and ( :selState = ' '
or :selState = custstate )
and ( :selClass = 0
or :selClass = custclass )

However, (as was recently pointed out to me :) this is functionally
equivalent and might perform better...
select <...>
from <...>
where
(:selName = ' '
or :selName like custname )
and (:selState in ( ' ', custstate )
and (:selClass in (0, custclass )

Any combination of Name, state and class will be used to limit the
results returned.

HTH,
Charles




On Fri, Jul 29, 2011 at 12:54 PM, Sharon Strippoli
<sharonstrippoli@xxxxxxxxxxxxxxxxx> wrote:

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.