Could it be your variable @pattern is defined as fixed length character
field?
If either use a varying length variable or pad your fixed length variable
with %-sign.
But my favorite solution is the following, without modifying the original
variable:
SELECT ....
FROM ...
WHERE YourColumn like '%' concat Trim(:YourVariable) concat '%'
...
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Denis Robitaille
Gesendet: Friday, 13.2 2015 22:49
An: MIDRANGE-L@xxxxxxxxxxxx
Betreff: SQL RPG Where clause question
Hello all,
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 vaviable)
This does not retrieve any value (sqlstt = 2000)
I then tried the following:
@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;
I tough that maybe I needed the pattern string to be in quotes but no luck.
So, to make sure that there was no problem somewhere else, I tried:
exec sql declare cur_next_filter cursor for
select ch1 from nozzzdp
where dsl like '%A%' ;
...
exec sql fetch from cur_next_filter into :ds_key;
This works and returns some value.
Does that mean that I cannot use variable with the "like" statement when
using a cursor?
Any help will be appreciated
[cid:image001.jpg@01D047AC.F71A7E40]<
http://www.cascades.com/_home>
Denis Robitaille
Chef de service TI
Cascades Centre des technologies,
une division de Cascades Canada ULC
412 Marie Victorin
Kingsey falls(Québec) Canada J0A 1B0
T : 819 363 6130
Pour toute information sur les événements techniques, veuillez consulter
notre calendrier Groupwise CAS_CALENDRIER_TI
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
