RE: SQL RPG Where clause question

Denis,

Assure toi que si @patern est 10 de long que le champ contient '%A%%%%%%%%'
ce devrais fonctionner je l'utilise tout le temps.

Marc


-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Denis
Robitaille
Sent: Friday, February 13, 2015 3:53 PM
To: RPG400-L
Subject: 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@01D047A3.F8A700C0]<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 RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.