The varying length variable did the trick.
Thanks everyone, I did not think of it.
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
-----Message d'origine-----
De : MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de Vernon Hamberg
Envoyé : 14 février 2015 17:03
À : Midrange Systems Technical Discussion
Objet : Re: SQL RPG Where clause question
Denis
I was curious about this, so I went to the SQL Programming Concepts PDF and found the following - the first item may apply, to use varying length host variables.
Special considerations for LIKE
- When host variables are used in place of string constants in a search pattern, you should consider using varying length host variables. This allows you to:
-- Assign previously used string constants to host variables without any change.
-- Obtain the same selection criteria and results as if a string constant was used.
- When fixed-length host variables are used in place of string constants in a search pattern, you should ensure the value specified in the host variable matches the pattern previously used by the string constants.
All characters in a host variable that are not assigned a value are initialized with a blank.
For example, if you did a search using the string pattern 'ABC%', these are some of the values that could be returned:
'ABCD ' 'ABCDE' 'ABCxxx' 'ABC '
For example, if you did a search using the search pattern 'ABC%'
contained in a host variable with a fixed length of 10, these are some the values that could be returned assuming the column has a length of 12:
'ABCDE ' 'ABCD ' 'ABCxxx ' 'ABC '
Note that all returned values start with 'ABC' and end with at least six blanks. This is because the last six characters in the host variable were not assigned a specific value so blanks were used.
If you wanted to do a search on a fixed-length host variable where the last 7 characters could be anything, you would search for 'ABC%%%%%%%'.
These are some values that could be returned:
'ABCDEFGHIJ' 'ABCXXXXXXX' 'ABCDE' 'ABCDD'
HTH
Vern
On 2/13/2015 3:49 PM, Denis Robitaille wrote:
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
