Re: Better SQL performance than SUBSTRING

[Charles Wilt <charles.wilt@xxxxxxxxx>]

​drop the "table" before the table name...

​ create index MY_TABLE_SUBSTRING on MY_TABLE
  (SUBSTRING(MY_COLUMN,5,6))

Charles

On Fri, May 4, 2018 at 12:05 PM, Justin Taylor <JUSTIN@xxxxxxxxxxxxx> wrote:

> 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