RE: SQLRPG with LIKE

Don't you mean
LIKE :FICTRIM

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





"Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
11/17/2004 10:19 AM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
"'RPG programming on the AS400 / iSeries'" <rpg400-l@xxxxxxxxxxxx>
cc

Fax to

Subject
RE: SQLRPG with LIKE






> From: Rick.Chevalier@xxxxxxxxxxxxxxx
> 
> What value is in FIC?  SQL wants something like this 'value%'.  If FIC
> contains the value only without % it will fail.  There is another
special
> character that works with LIKE but I can't recall it at the moment.

To add to this, with a fixed-length field even a '%' won't necessarily
work.

For example, if FIC contains "ABC%      ", it won't do what you expect,
because SQL interprets this as "starts with ABC and ends with six
blanks, with anything in between".  You'll get ABC and ABCD, but not
ABCDE.

Two workarounds:

1. Pad your compare field with wildcards.  Use either % or _ (underscore
matches any single character).  Thus, instead of "ABC%      ", use
"ABC%%%%%%%".

2. Use a varying length string.  Set the varying length string equal to
the %trim() value of your compare.

D FICTRIM         S             10A   Varying

C                   Eval      FICTRIM = %trim(FIC)
C/EXEC SQL 
(...)
C+ WHERE DBXATR = 'PF' AND DBXLIB = :BIB AND DBXFIL 
C+ LIKE :FIC 
C/END-EXEC


Joe

--
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.