Efficiency is a hard topic to tackle. But generally speaking, static sql
will run a bit faster than the dynamic sql. Because statuc sql does not
require a lot of warm up time.
As for complex criteria, you could try specify both the >= and <= conditions
for each optional filter field that you have.
Using your example (Assuming that coid is the department field), you can
change your statement to be like:
Exec-sql
declare C1 cursor for
select * from oncontac00
join ondept00
on coid = onid
where
colast >= :colast_var and
coid >= :coid_low and coid <= :coid_hi
order by Colast;
In your RPG program, if user did not specified a department filter value,
then initialize coid_low to *loval and coid_hi to *hival. If user specified
a department value that he wants, then initialize both coid_low and coid_hi
to the value that he has specified. Hopefully this make sense...
"Smith, Mike" <Mike_Smith@xxxxxxxxxxxxxxxx> wrote in message
news:mailman.25241.1295894288.2702.rpg400-l@xxxxxxxxxxxx...
I'm working on a sql subfile program and I'm trying to make use of
parameter markers.
I can get it working when only doing a Position to marker, but now I
have introduced more complex criteria and I can't figure out how to make
this work effectively.
Subfile has a Position TO field. This should be a parameter marker.
Also I'm allowing user to select a specific department. This should be
a parameter marker
selct = 'select * from oncontac00 join ondept00 +
on coid = onid +
where colast >= ? +
and coid = ? +
order by COlast' ;
Do I need to set up multiple select statements for all possible
conditions?
Do I need to set up multiple open statements to coincide with the
different conditions also.
Any examples would be appreciated.
Thanks
Mike
NOTICE: This message, including any attachment, is intended as a
confidential and privileged communication. If you have received this message
in error, or are not the named recipient(s), please immediately notify the
sender and delete this message.
midrange.com
