Special sorting

[Beatrix Willius]

Hi Thomas,

yes, you need to define the function and then you can call it in the cursor.

I've got a nice function:

create or replace function PathSort(thePath string) returns string begin case when lower(list_nthitem(thePath, 1, ':')) = 'inbox' then return concat('1:', replace(replace(thePath, ' ', 'zzzyyy'), ':', 'zzzxxx')); when lower(list_nthitem(thePath, 1, ':')) = 'sent' then return concat('2:', replace(replace(thePath, ' ', 'zzzyyy'), ':', 'zzzxxx')); when lower(list_nthitem(thePath, 1, ':')) = 'sent messages' then return concat('2:', replace(replace(thePath, ' ', 'zzzyyy'), ':', 'zzzxxx')); 
 
which I can then use in

select RecID, MailboxPath, WhereClause, PathSort(MailboxPath) from mailbox order by 4

As far as I remember I had to use the 4 instead of PathSort(MailboxPath).

HTH

On 25.03.2013, at 16:10, Thomas Flemming <tf at qvgps.com> wrote:

> is there a way to get a cursor sorted by a value, which not directly stored in that table, but which can be calculated with an own function from some fields of that table?
> 
> cur = db.SqlSelect("SELECT * FROM mytable
>                     ORDER BY myfunction(field1,field2)")

Mit freundlichen Grüßen/Regards

Trixi Willius

http://www.mothsoftware.com
Mail Archiver X: The email archiving solution for professionals