Odd SQL Problem

[Beatrix Willius]

Working with VStudio (Mac, 4RC1) I have an odd SQL problem.

The table called "Mailbox" has a field called "MailboxPath". I want  
to sort it with a custom sort function. Values starting with Inbox  
come first etc. The result should like the following:

MailboxPath			Value for sorting
Mailbox:Something	4:Mailbox:Something
Trash				3:Trash
Inbox				1:Inbox
Inbox:Account		1:Inbox:Account

The following SQL works okay:

select MailboxPath,
case
		when list_nthitem(MailboxPath, 1, ':') = 'Inbox' then
			concat('1:', MailboxPath)
		when list_nthitem(MailboxPath, 1, ':') = 'Sent' then
			concat('2:', MailboxPath)
		when mailboxPath = 'Trash' then
			concat('3:', MailboxPath)
		else
			concat('4:', MailboxPath)
	end
from mailbox

Now if I create a function

create or replace function PathSort(thePath string)
returns string
begin
	case
		when list_nthitem(thePath, 1, ':') = 'Inbox' then
			return concat('1:', thePath);
		when list_nthitem(thePath, 1, ':') = 'Sent' then
			return concat('2:', thePath);
		when thePath = 'Trash' then
			return concat('3:', thePath);
		else
			return concat('4:', thePath);
	end case
end

and test it with

select mailboxpath, PathSort(MailboxPath) from Mailbox order by 2

then I get the following result:

MailboxPath			Value for sorting
Mailbox:Something	4:Mailbox:Something
Trash				3:Trash
Inbox				4:Inbox
Inbox:Account		4:Inbox:Account

Am I missing something?
		

Mit freundlichen Grüßen/Regards

Trixi Willius

http://www.mothsoftware.com
Mail Archiver X: archive, clean and search email
http://www.beatrixwillius.de
Fractals, 3d landscapes etc.