RE: Sql parameter help

I would consider using Between :Field1 and :Field2 rather than >= :Field1 and <= Field2.

Something else that I avoid is "or" sql statement only because it gets ugly.


i.e. instead of this

Where company = 1
and (:HostVar = 0 or region = :HostVar)

I would do.

Where company = 1
and :HostVar in (0,region)

So if I wanted all regions, :HostVar would equal 0.

Just some food for thought.

Pick me apart if you like.


-----Original Message-----
From: "hockchai Lim" <lim.hock-chai@xxxxxxxxxxxxxxx>
To: rpg400-l@xxxxxxxxxxxx
Date: 01/24/2011 02:47 PM
Subject: Re: Sql parameter help
Sent by: rpg400-l-bounces@xxxxxxxxxxxx



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.