Re: Embedded SQL and Parameter Markers

Am 17.02.2022 um 18:23 schrieb Craig Richards <craig@xxxxxxxxxxxxxxxx>:

Thanks all for your helpful replies, that looks a lot cleaner.

A quick addition - I'm struggling to see how I could use static SQL and
Case Statement with the following:

If Filter1 <> *Blanks;
SqlWhere += ' And RegExp_Like(Field1, ''' + Filter1 + ''',1,''i'') ';
EndIf;

In this particular case (where I'm really using the regular expression for
a case insensitive search) I recall from something Birgitta posted that
instead of using a regular expression, I could instead:

set option srtSeq = *LangIdShr;

And use a regular LIKE predicate:

E.g something along the lines of:

Exec Sql Select Count(1) Into :Count from File
Where Field1 like
Case
When :Filter1 = '' Then Field1
Else :Filter1
End;

Which leaves me with two question:

1. I'm always a little wary of using the LIKE predicate on big files, but
in this example for the case where Filter1 is empty - the pattern
expression contains no wildcard characters (so to speak) so maybe it isn't
so expensive?
2. I'm still not sure of a good way to handle the Regular Expression in an
elegant, dynamic way.

Thanks kindly
--
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