Re: SQL RPG Where clause question

On 13-Feb-2015 15:49 -0600, Denis Robitaille wrote:

I want to extract record from a file where a field matches a
pattern. To do so, I use the « LIKE » instruction in the WHERE
clause. Here is a strip down version:

@patern = '%' + %trim(@filter) + '%';
exec sql declare cur_next_filter cursor for
select ch1 from nozzzdp
where dsl like :@patern ;
...
exec sql fetch from cur_next_filter into :ds_key;

The value of the @filter variable is "A" (I want everything with an
A in the dsl variable)

This does not retrieve any value (sqlstt = 2000)

<<SNIP>>

Any help will be appreciated

Besides what else has been explained and offered here already [as well as in response to the duplicate topic in the RPG forum], the following should suffice in the given scenario:

exec sql declare cur_next_filter cursor for
select ch1
from nozzzdp
where dsl like '%' concat TRIM(:@filter) concat '%'
;

Because the SQL TRIM scalar function will result in a varying length string, can be evaluated once per query [versus evaluated for every row and thus can perform without negative impact], and has the same effect as the RPG %trim(), the LIKE predicate should work just like how the doc reference suggests Vern made; i.e. same as if "using varying length host variables".