Re: Sql parameter help

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.