Re: Useful SQL tip for handling optional parameters

[Michael Schutte <mschutte369@xxxxxxxxx>]

<snip>
exec sql
     delete from FILE
       where FIELD1 = :parm1 and
                    Field2 = Case When Value2 > '' Then :Parm2 Else Field2;
</snip>

Often times, I use case in the order by and allow null to be returned in
the case.  I wonder how this would return if you case both sides.


exec sql
     delete from FILE
       where FIELD1 = :parm1 and
                   case when :Parm2 > "" then  Field2 end = Case When Parm2
> '' Then :Parm2 End;


I'm thinking it would fail because you cannot equate null to null.



On Thu, Dec 8, 2016 at 1:00 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

> Yet a different solution:
>
> 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;
>    endif;
>
>    exec sql
>      delete from FILE
>        where FIELD1 = :parm1 and
>                     Field2 = Case When Value2 > '' Then :Parm2 Else Field2;
>
>
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training them
> and keeping them!"
> „Train people well enough so they can leave, treat them well enough so they
> don't want to.“ (Richard Branson)
>
>
> -----Ursprüngliche Nachricht-----
> Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von David
> Gibbs
> Gesendet: Wednesday, 07.12 2016 16:59
> An: rpg400-l@xxxxxxxxxxxx
> Betreff: Useful SQL tip for handling optional parameters
>
> 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
>
> --
> 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