× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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".


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.