Re: Like in embedded SQL

Note that a table (or index) scan is unavoidable in this case since
the OP is using a wildcard at the front of her compare value.

As a refresher for the archives, when you specify trailing wildcards
such as LIKE 'A200%' the system can use an index if it exists (or
build a temporary one) to determine quickly which rows match the
criteria. However, when you specify leading wildcards such as LIKE
'%A200' or LIKE '%A200%' then the system has to do a full scan to find
any matching rows. If an index exists, it could do an index scan (but
that's not the same as actually using the index) otherwise you get a
full table scan.

At least with the standard DB2 functionality. I believe that the
optional "text extenders" include some functionality to make finding
rows LIKE '%A200' or LIKE '%A200' as fast finding rows LIKE 'A200%'

HTH,

Charles

On Tue, Dec 29, 2009 at 6:04 PM, Vern Hamberg <vhamberg@xxxxxxxxxxx> wrote:

If you column were varying length and filled with a trimmed value, you
would not need the trailing wildcard, because it would have stopped
processing at the length of the value. But you can't count on that,
right? So either the strip() function or the trailing wildcard will do
the trick - of course, this means that a table scan is likely to take place.

Vern

Joep Beckeringh wrote:

Terri,

Assuming LLOC is 6 characters long, it contains '2A200 '. The trailing
space is your problem; use TRIM(LLOC) and you should be fine.

Joep Beckeringh


Op 29-12-09 20:39, THarteau@xxxxxxxxxxxxxxxxxx schreef:

Hi,
         Are there any issues when using LIKE in an SQL declare statement?
I have the following line:
/EXEC SQL
+ DECLARE C6 CURSOR FOR SELECT LPROD, LWHS, LLOT, LLOC, LOPB, LISSU,
+  LADJU, LRCT, LIALOC, LISTAT, WLSTS FROM ILIL01 JOIN ILM ON WWHS =
+  LWHS AND WLOC = LLOC WHERE LWHS = :KEYWHS AND LPROD = :PROD AND L
+ LOC LIKE '%200' ORDER BY WLSTS, LLOC FOR FETCH ONLY
/END-EXEC

It seems to work when it finds 11A200 or 22A200, but it is not returning
2A200. Any ideas? Thanks!

<===================================================>

Terri Harteau
Felker Brothers Corporation
****************
"Do not follow where the path may lead. Go instead where there is no path
and leave a trail."
Ralph Waldo Emerson

****************

--
This is the RPG programming on the IBM i / System i (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.