Re: Useful SQL tip for handling optional parameters

[Charles Wilt <charles.wilt@xxxxxxxxx>]

David,

An alternative format I've seen that's useful
where FIELD1 = :parm1 and
       :value2 in ('*ALL', FIELD2);

Charles


On Wed, Dec 7, 2016 at 8:59 AM, David Gibbs <david@xxxxxxxxxxxx> wrote:

> Folks:
>
> I just had a situation where I needed to handle an optional parameter on a
> procedure, where the parameter was being used for record selection in a SQL
> statement.
>
> I didn't want to deal with multiple SQL statements based on if the
> parameter was passed or not.
>
> So here's routine I put together ...
>
> dcl-proc myproc export;
>   dcl-pi *n;
>     parm1 char (10) const;
>     parm2 char (10) const options(*nopass);
>   end-pi;
>
>   dcl-s value2 like(parm2);
>
>   if %parms > 1;
>     value2 = parm2;
>   else;
>     value2 = '*ALL';
>   endif;
>
>   exec sql
>     delete from FILE
>       where FIELD1 = :parm1 and
>        (FIELD2 = :value2 or :value2 = '*ALL');
>
> end-proc;
>
> I wasn't sure if the ":hostvalue = 'constant'" criteria was going to work,
> but it does appear to work fine.
>
> david
>
> --
> IBM i on Power Systems: For when you can't afford to be out of business!
>
> I'm riding a metric century (100 km / 65 miles) in the American Diabetes
> Association's Tour de Cure to raise money for diabetes research, education,
> advocacy, and awareness.  You can make a tax deductible donation to my ride
> by visiting http://mideml.diabetessucks.net.  My goal is $6000 but any
> amount is appreciated.
>
> You can see where my donations come from by visiting my interactive
> donation map ... http://mideml.diabetessucks.net/map (it's a geeky thing).
>
> I may have diabetes, but diabetes doesn't have me!
> --
> This is the RPG programming on the IBM i (AS/400 and iSeries) (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.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: http://amzn.to/2dEadiD