RE: SQL Question

Hi, Sudha

This is not exactly intuitive. The reference manual says, when talking
about parameter markers:

Note: When the pattern specified in a LIKE predicate is a parameter
marker, and a fixed-length character host variable is used to replace the
parameter marker; specify a value for the host variable that is the
correct length. If you do not specify the correct length, the select will
not return the intended results.

For example, if the host variable is defined as CHAR(10), and the value
WYSE% is assigned to that host variable, the host variable is padded with
blanks on assignment. The pattern used is ('WYSE%     '). This pattern
requests the database manager to search for all values that start with
'WYSE' and end with'      '. If you intended to search for only the values
that start with 'WYSE' you should assign the value 'WSYE%%%%%%' to the
host variable.

Seems that variable-length host variables might work fine.

Another aspect, little used, is the ESCAPE parameter, which lets you use
the wildcard characters in the string. The following shows how, so that a
'+' preceding a '%' or '_ means to treat those as real characters, not
wildcards.

C1 LIKE 'AAAA+%BBB%' ESCAPE '+'

Cool

Vern

At 01:15 PM 1/2/2003 -0600, you wrote:

I have seen this too.  I have had to pad the host variable field  with
*all(%) for LIKe keyword and we are in v5r1.
Thanks,
Sudha

-----Original Message-----
From: Smith, Graham [mailto:GSmith2@scj.com]
Sent: Thursday, January 02, 2003 11:53 AM
To: 'midrange-l@midrange.com'
Subject: RE: SQL Question


Rick,

I wish you were right but I've bitter experience behind me in this case.

Stumbled across this years back and found that in a Host variable the single
% didn't work correctly but padding full length of host variable worked
fine.

Go figure!

Cheers
Graham