RE: Sql parameter help

["Schutte, Michael D" <Michael_Schutte@xxxxxxxxxxxx>]

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.