Re: SQL RPG Where clause question -- MIDRANGE-L

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