Got a bit of an edge case for sorting. The sorting works in Xojo, but not quite in Valentina.
The data is similar to:
a
a:::b
a:::b:::c
Now if the data is
a
a:::b
a:::b-2
a:::b:::c
sorting doesn't work. a:::b:::c should be following a:::b but it's after a:::b-2 . In Xojo I use the trick to replace ":::" with chr(1). This is why I asked for the chr function some days ago.
Why does't Valentina sort correctly? I even converted the data to hex because I can't view the data as binary and Xojo sorts the result just fine.
Here is the function I'm using:
CREATE OR REPLACE
FUNCTION PathSort(thePath STRING) RETURNS STRING BEGIN CASE
WHEN LOWER(list_nthitem(thePath, 1, ':::')) = 'inbox' THEN RETURN concat('1:', REPLACE(thePath, ':::', chr(1)));
WHEN LOWER(list_nthitem(thePath, 1, ':::')) = 'sent' THEN RETURN concat('2:', REPLACE(thePath, ':::', chr(1)));
WHEN LOWER(list_nthitem(thePath, 1, ':::')) = 'sent messages' THEN RETURN concat('2:', REPLACE(thePath, ':::', chr(1)));
WHEN thePath = 'Trash' THEN RETURN concat('3:', REPLACE(thePath, ':::', chr(1)));
WHEN thePath = 'Spam' THEN RETURN concat('4:', REPLACE(thePath, ':::', chr(1)));
WHEN LOWER(list_nthitem(thePath, 1, ':::')) = 'views' THEN RETURN concat('5:', REPLACE(thePath, ':::', chr(1)));
WHEN LOWER(list_nthitem(thePath, 1, ':::')) = 'Mailboxes' THEN RETURN concat('6:', REPLACE(thePath, ':::', chr(1)));
ELSE RETURN concat('7:', REPLACE(thePath, ':::', chr(1)));
END CASE END
The SQL using this function is:
SELECT RecID, MailboxPath, PathSort(MailboxPath) FROM mailbox ORDER BY 3
PS: I'd use the Xojo sort implementation if I had a good idea how to add the database specific sorting to a database-agnostic control that simply displays a cursor.
Regards
Beatrix Willius