Re: Is this an SQL problem or quirk

FWiW: All of the pattern-expression values are upper cased. Unless the data being scanned had all been inserted into the file using upper case, the sort sequence used should be for a shared weight [between upper and lower case characters] to include any mixed case variations of the data; e.g. using a *LANGIDSHR sort sequence. Or UPPER(cadd#) could be used in most cases.

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/nls/rbagsdb2qmansqlsortseq.htm
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzajq/rzajq.pdf

Regards, Chuck

Alan Shore wrote:

<<SNIP>>

The project is to attempt to "pull" this data out and place
into a separate field. I came up with the following idea:

I have a file (APTSCAN) with one 12 character field (APTSCANDEF)
containing (at the moment) the following data, % characters
included:

%APT %
%APT #%
%APT. #%
%APT.%
%BLDG%
%DEPARTMENT%
%DEPT %
%FLOOR %
%FLR %
%FLR.%
%P O BOX%
%P.O. BOX%
%PO BOX%
%PO.BOX%
%POBOX%
%ROOM %
%UNIT %
%UNIT #%

I then ran the following SQL statement

SELECT * FROM custadrp a, APTSCAN b
WHERE a.CADD1 like b.APTSCANDEF or
a.CADD2 like b.APTSCANDEF

and it worked perfectly. All the relevant customer records
were displayed.

<<SNIP>>