Re: Varchar and numerical sort sequence

[Craig Richards <craig@xxxxxxxxxxxxxxxx>]

Hi Birgitta,

Thanks for your reply.

I think I understand what you are saying.
I believe that an index is needed, I think that applying a scalar function
to the ORDER BY column won't be fast enough.

If I understand correctly, your idea is to have a view with the
Right-Adjusted column in it.
And build a Derived Index on the main table which uses the same expression
as the View Column.

I haven''t done anything with derived indices before.

I'm getting an error trying to create one with the nested functions:
e.g:

create index       vi on glevarc00
(lpad(trim(evttype), 30)  as RAdj)
===Index expression 0 not valid===

I can get it to work for a single function:
create index       vi on glevarc00
(trim(evttype)  as RAdj)  S

I think my syntax is correct in the failing attempt?

thanks and regards,
Craig

On Thu, 11 Apr 2019 at 11:52, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

> What about using LPAD for right adjusting the content (you don't have to
> care where the column includes invalid numeric values or not) and order the
> result?
>
> ORDER BY LPAD(Trim(YourCharCol), 15) Desc
> Fetch 10 Rows only
>
> In either way I'd define a view which includes the an additional column
> with
> the LPAD function.
> ... you easily can add an ORDER BY over the additional column in your
> program.
>
> Additionally I'd create a derived index containing a Key Column (with the
> LPAD function).
> If the syntax of the view column and the syntax of the Key Column in the
> index match, the optimizer can use the index. ...and return the requested
> very quick.
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training them
> and keeping them!"
> „Train people well enough so they can leave, treat them well enough so they
> don't want to.“ (Richard Branson)
>
>
> -----Original Message-----
> From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Craig
> Richards
> Sent: Donnerstag, 11. April 2019 09:58
> To: RPG programming on the IBM i (AS/400 and iSeries)
> <rpg400-l@xxxxxxxxxxxxxxxxxx>
> Subject: Varchar and numerical sort sequence
>
> Someone was asking a question on a python board that I subscribe to.
>
> He has a table in a MySQL database which contains a VarChar which
> represents
> a Confirmation Number.
>
> It may be NULL.
> And I assume it may contain invalid numerics.
>
> He wants to be able to (for example) retrieve the last 10 Confirmation
> Numbers based on a numerical sort sequence.
>
> Obviously he can't order by the existing VarChar Confirmation Number
> because
> it's alpha and he will get results like:
> 901
> 900
> 90
> 899
> 898
>
> So he has been using CAST to convert the NOT NULL values to INT and then
> sorting on that but his response time for the query is very slow due to the
> volume of data (and also I think it's part of a larger query)
>
> I don't think views will help his cause as you can't use ORDER BY.
>
> My first thought was - can he add another column to the table (over which
> he
> could add a non-unique index) - make it NOT NULLABLE and default to 0.
> Then in the application, modify it when a valid numeric value is entered in
> the Confirmation Numer column.
>
> If it's not viable to modify the application - would it make sense to use
> an
> UPDATE TRIGGER to keep the new column maintained.
>
> I don't actually recall having modified the after image in a trigger
> program
> - is that a reasonable thing to do?
>
> Any advice/comments gratefully accepted.
> thanks,
> Craig
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list To post a
> message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
> change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives at
> https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link:
> https://amazon.midrange.com
>
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com