Re: SQL Like Question. -- RPG400-L

Interesting,

I just ran my own test... interactively using STRSQL

This did return records
Select * from ...
Where LNAME = 'GIBSON            '
---

However,

Select * from ...
Where LNAME = 'GIBSON'
Did not...

---

I know that field sizes are the same so I would think that the SQL
statement would have worked, maybe the issue was somewhere else.  Sorry to
bother you all.




                                                                           
             rob@xxxxxxxxx                                                 
             Sent by:                                                      
             rpg400-l-bounces@                                          To 
             midrange.com              RPG programming on the AS400 /      
                                       iSeries <rpg400-l@xxxxxxxxxxxx>     
                                                                        cc 
             07/20/2006 04:03                                              
             PM                                                    Subject 
                                       Re: SQL Like Question.              
                                                                           
             Please respond to                                             
              RPG programming                                              
              on the AS400 /                                               
                  iSeries                                                  
             <rpg400-l@midrang                                             
                  e.com>                                                   
                                                                           
                                                                           




Simple test in STRSQL
SELECT * FROM QTEMP/RPG400
....+.
FNAME
TIM
TIMMY
PETE
******

SELECT * FROM QTEMP/RPG400
WHERE FNAME LIKE 'TIM'
No data selected for output.

SELECT * FROM QTEMP/RPG400
WHERE FNAME LIKE 'TIM%'
....+.
FNAME
TIM
TIMMY
******

So, in summary, yes, you do have to use the special characters.
You previous experience was either due to some good mushrooms, or, you
filled the size of the field.  For example if I drop down the size of the
name field.
CREATE TABLE QTEMP/RPG400a (FNAME CHAR (3 ) NOT NULL WITH DEFAULT)
INSERT INTO QTEMP/RPG400b VALUES('TIM')
SELECT * FROM QTEMP/RPG400a
WHERE FNAME LIKE 'TIM'
....+
FNAME
 TIM
*****

There's the exception to prove the rule.

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





Michael_Schutte@xxxxxxxxxxxx
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
07/20/2006 03:32 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
rpg400-l@xxxxxxxxxxxx
cc

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


--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.


--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.