Re: Sql parameter help

Two options....

With dynamic SQL, build up the statement incrementally...

selct = 'select * from oncontac00 join ondept00 +
on coid = onid ';

// adding a dummy where means I can simply AND my real criteria
// otherwise I have to track which is the first condition I add and
leave off an AND
selct += ' where 1 = 1 '

if w_Colast <> *BLANKS;
selct += ' and colast >= ? ';
endif;

if w_coid <> *BLANKS;
selct += ' and coid = ? ';
endif;


Or with either dynamic or static SQL, you can condition the statement
itself (don't forget the parens!)

selct = 'select * from oncontac00 join ondept00 +
on coid = onid +
where (colast = ' ' OR colast >= ? )+
and (coid = ' ' OR coid = ? )+
order by COlast' ;

HTH,
Charles

On Mon, Jan 24, 2011 at 1:34 PM, Smith, Mike
<Mike_Smith@xxxxxxxxxxxxxxxx> wrote:

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.
--
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.