Re: Is this an SQL problem or quirk

[CRPence <CRPbottle@xxxxxxxxx>]

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