Re: Embedded SQL and Parameter Markers

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