Re: Embedded SQL and Parameter Markers

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Assuming you're on 7.3+ and reasonably current on PTFs...

Take a look at USING SUBSET on EXECUTE and OPEN with Extended indicators
https://www.ibm.com/support/pages/node/6174207

It allows for exactly what you're asking for...
Build a dynamic statement with a variable number of parameter markers yet
have a single open/execute with all possible variables.

Charles


On Thu, Feb 17, 2022 at 6:36 AM Craig Richards <craig@xxxxxxxxxxxxxxxx>
wrote:

> I'm building a few "Work With" screens with filters at the moment
> Fairly typical maintenance programs where several of the columns shown in a
> subfile have a "filter" field above them for refining the record selection.
>
> Because the number of filters used is variable, I have traditionally
> created dynamic SQL by building up a WHERE clause in a string and adding in
> any of the filter fields which have been entered on the screen by the user.
>
> I was wondering if there is a reasonable way to do this using parameter
> markers.
> Given that the number of filters entered is variable, so would the list of
> parameter markers be.
>
> To illustrate - I'm doing something like (simplified for brevity):
>
> SqlStm = 'Select ... From ...';
>
> If Filter1 <> *Blanks;
>    SqlWhere += 'and Field1 = Filter1'
> EndIf;
>
> If Filter2 <> *Blanks;
>    SqlWhere += 'and Field2 = Filter2'
> EndIf;
>
> If SqlWhere <> *Blanks;
>    SqlStm += SqlWhere;
> EndIf;
>
> etc.
>
> In this simple example I could end up with:
> - Select * from File
> - Select *from File where Field1 = Value1
> - Select *from File where Field2 = Value2
> - Select *from File where Field1 = Value1 and Field2 = Value2
>
> Is there a better way to build this dynamic sql?
>
> Thanks in advance for any suggestions.
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com