RE: SQL Like Question.

["Hatzenbeler, Tim" <thatzenbeler@xxxxxxxxxxxxx>]

I have ran into problems using LIKE when I don't leave the trailing blanks..

For example.

FldA = STS3576   and its 10 chars long.

If you use

Where flda like 'STS3576' it will not match, but if you add the spaces flda
like 'STS3576   '

It does work...At least that what it does on my machine..  Maybe a ptf
thing, but I just tried it.  

tim

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Michael_Schutte@xxxxxxxxxxxx
Sent: Thursday, July 20, 2006 12:32 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: SQL Like Question.


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