Re: Better SQL performance than SUBSTRING

That doesn't seem to work for me. The statement ran, but no object was
created.

Here's my statement:
create index MY_TABLE_SUBSTRING on table MY_TABLE
(SUBSTRING(MY_COLUMN,5,6))


Here's the message iACS gave me on the create:
SQL State: 01505
Vendor Code: 143
Message: [SQL0143] Statement CREATE INDEX AUXILIARY TABLE ignored. Cause .
. . . . : Statement CREATE INDEX AUXILIARY TABLE is not supported by DB2
for IBM i. It has been ignored. Recovery . . . : No action is
required.
Statement ran successfully, with warnings (6 ms)


IBMi 7.3, PTF's current as-of 07 Apr 18
iACS 1.1.7.3




-----Original Message-----
From: Steve Needles [mailto:Stephen.Needles@xxxxxxxxxxx]
Sent: Friday, May 04, 2018 10:09 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: Better SQL performance than SUBSTRING

Yes...you can create an index using a substring:

CREATE INDEX Table_Index
ON Table
(substr(Column,case when length(trim(Column))-7 <= 0
then 1
else length(trim(Column))-6 end
, 7));

Generate a key using the last characters in the string no matter the total
length of the string

Steve Needles

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD