|
midrange.com
Thanks for the input, that was certainly something that might cause the
problem. I just verified that the field on the screen is exactly the same
size of the field in the file and there are not any %trims being performed.
"Hatzenbeler,
Tim"
<thatzenbeler@cli To
nitech.net> "'RPG programming on the AS400 /
Sent by: iSeries'" <rpg400-l@xxxxxxxxxxxx>
rpg400-l-bounces@ cc
midrange.com
Subject
RE: SQL Like Question.
07/20/2006 03:54
PM
Please respond to
RPG programming
on the AS400 /
iSeries
<rpg400-l@midrang
e.com>
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
--
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
