Re: Is this an SQL problem or quirk

DeLong, Eric wrote:

<<SNIP>>

I'm wondering about the APTSCAN file. I know I've been clobbered
in the past when passing a "LIKE" pattern into embedded SQL, where
the blanks at the end of the field become part of the search
string.... Perhaps you need to make your APTSCAN.APTSCANDEF field
varying length, so that your scan tokens do not carry unwanted
blanks.

However, this would not explain why YOU get the results you are
looking for....

Alan Shore wrote:

> 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.%
<<SNIP>>

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.

I then sent the above SQL to an associate who has to do something similar. He ran the same sql and the results were
COMPLETELY different
<<SNIP>>

With that idea... Since the /potentially ran SQE/ case is functional and the other /potentially ran CQE/ case is nonfunctional, I wonder if SQE is treating the CHAR(12) [field as expression] as VARCHAR(12), thus causing its selection to function as expected. To verify if that might be a possible cause, padding the values\patterns in the scan file with the percent sign [instead of blanks] would then see both scenarios functional. Or I suppose effectively the same test is to try the following variation on the SQL to see if both sessions function as expected when using the RTRIM which gives a VARCHAR resultant data type.

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

Note: From the original posting, '%APT #%' as the pattern-expression is redundant when also including the pattern-expression '%APT %' in the test of those LIKE predicates. Similar duplication in other example(s). Also the join is a /cross product/ join so the number of rows to process goes up very fast as new patterns are added. I believe since v5r1 the LIKE is enabled for a reusable ODP, and if there is going to be a report program to generate the rows, it may be better to repeat the query for each pattern; pre-load the table into memory with SETOBJACC. However since the given patterns require\force the query engine to perform a full table scan, the best option given no limits are exceeded [statement size or conditions tested], is probably to go fully dynamic and just build the statement concatenating all of the pattern expressions from the pattern file, or build the full statement adding the new patterns to the statement rather than to a file of patterns; e.g.:

select *
from custadrp a
where
cadd1 like '%APT %' or cadd2 like '%APT %'
or cadd1 like '%APT. %' or cadd2 like '%APT. %'
or cadd1 like '%BLDG%' or cadd2 like '%BLDG%'
/* etc. "OR" each for each new pattern */

Regards, Chuck