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