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