Re: Embedded SQL and Parameter Markers

I would try

where ....

and case when trim(:Filter1)='' then 1
else regexp_count(Field1, trim(:Filter1), 1, 'i') end > 0
....
HTH
Daniel

Thanks Daniel, I never thought of that - nice one.


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


Thanks Charles - that does look interesting and good to know.
It's definitely more work than using the static method that Birgitta
suggested, in that you have to add in all of the logic to build the correct
statement and set the indicator variables, but I can also see some cases
where it might be useful.

I have to admit I was a bit confused by the link you shared above as it
left me a bit unsure how to code it in an SQLRPGLE program but I
happened upon this link
https://www.itjungle.com/2020/06/15/guru-subset-on-execute-and-open/ which
helped me out.
I did manage to get an example of it working on my machine.

Thanks all for your posts, very interesting and I've learned a lot.