SQL Like Question.

[Michael_Schutte@xxxxxxxxxxxx]

I have a question that I don't know if I can search on in the midrange
archives (search function is currently disabled). Anyway, I've created this
SQL where clause like this.

WHERE  PRER = :A1ER
  AND (PRL01  = :A1L01 Or :A1L01 = '     ')
  AND (PRL02  = :A1L02 Or :A1L02 = '     ')
  AND (PRL03  = :A1L03 Or :A1L03 = '     ')
  AND (PRL04  = :A1L04 Or :A1L04 = '     ')
  AND (PRSEX  = :A1SEX Or :A1SEX = ' ')
  AND (PRSTA1 = :A1STA Or :A1STA = ' ')
  AND (SUBSTRING(PREN,6,4) = :A1EN4 Or :A1EN4 = '    ')
  AND (PRFNM LIKE
       (CASE
         WHEN :FName <> ' ' THEN TRIM(BOTH ' ' FROM :FName)
         ELSE '#$%¢#%¢#$%¢#$¢#$%¢'
        END)
     OR :FName = ' ')
  AND ((PRLNM LIKE
       (CASE
         WHEN :LName <> ' ' THEN TRIM(BOTH ' ' FROM :LName)
         ELSE '#$%¢#%¢#$%¢#$¢#$%¢'
           ELSE '#$%¢#%¢#$%¢#$¢#$%¢'
          END)
       OR :LName = ' ')
     OR (PRPLNM LIKE
         (CASE
           WHEN :LName <> ' ' And :A1PLNmYN = 'Y' THEN
               TRIM(BOTH ' ' FROM :LName)
           ELSE '#$%¢#%¢#$%¢#$¢#$%¢'
          END)
       OR (:LName = ' ' And :A1PLNMYN = 'Y')))

I know that it appears complicated but ignore the for #$%¢#%¢#$%¢#$¢#$%¢
strings the moment (that's only being used because I know that it's
completely impossible for either a first name or last name could equal
that.

My question has to do with the LIKE keyword.  Do you have to have a string
using the special characters in order for it to work?  I may be imagining
this but I could have sworn that when I ran this a few weeks ago the
program did work when the :LName field equaled SCHUTTE.  But today it
return no records unless I enter SCHUTTE* (which btw is converted to % in
the RPG program).

Also note, that I don't need a solution as I have fixed the issue by adding
another OR. I just wanted to know if I was crazy or not...

This is what it looks like now.

WHERE  PRER = :A1ER
  AND (PRL01  = :A1L01 Or :A1L01 = '     ')
  AND (PRL02  = :A1L02 Or :A1L02 = '     ')
  AND (PRL03  = :A1L03 Or :A1L03 = '     ')
  AND (PRL04  = :A1L04 Or :A1L04 = '     ')
  AND (PRSEX  = :A1SEX Or :A1SEX = ' ')
  AND (PRSTA1 = :A1STA Or :A1STA = ' ')
  AND (SUBSTRING(PREN,6,4) = :A1EN4 Or :A1EN4 = '    ')
  AND (PRFNM LIKE
       (CASE
         WHEN :FName <> ' ' THEN TRIM(BOTH ' ' FROM :FName)
         ELSE '#$%¢#%¢#$%¢#$¢#$%¢'
        END)
     OR :FName = ' '
     OR PRFNM = :FNAME)
  AND ((PRLNM LIKE
       (CASE
         WHEN :LName <> ' ' THEN TRIM(BOTH ' ' FROM :LName)
         ELSE '#$%¢#%¢#$%¢#$¢#$%¢'
           ELSE '#$%¢#%¢#$%¢#$¢#$%¢'
          END)
       OR :LName = ' '
       OR PRLNM = :LName)
     OR (PRPLNM LIKE
         (CASE
           WHEN :LName <> ' ' And :A1PLNmYN = 'Y' THEN
               TRIM(BOTH ' ' FROM :LName)
           ELSE '#$%¢#%¢#$%¢#$¢#$%¢'
          END)
       OR (:LName = ' ' And :A1PLNMYN = 'Y')
       OR (:LName = PRPLNM And :A1PLNMYN = 'Y')))


Thanks for your help.

Michael Schutte