× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.