I tried a similar (though a lot simpler) query in Fb 1.5.4, and was surprised to see that it apparently worked. I've never thought of using parameters in the ORDER BY clause. I have no idea whether what you tried should work or not, but what I would try if I were you, is to add one or two parenthesis around CASE (I've seen that help before, but I think that was a way of avoiding a syntax error, so it wasn't a case similar to yours) or modify the statement as follows:

SELECT
CASE :OrderBy
WHEN 0 THEN MOC_ITEMS_1.MOCITEMID
WHEN 1 THEN MOC_ITEMS_1.MOCCATEGORYID
WHEN 2 THEN MOC_ITEMS_1.LETTERNUMBER
WHEN 3 THEN MOC_ITEMS_1.SENDERPARTYID
WHEN 4 THEN MOC_ITEMS_1.RECIPIENTPARTYID
WHEN 5 THEN MOC_ITEMS_1.CURRENTOWNER
WHEN 6 THEN MOC_ITEMS_1.REFERREDNUMBER
END AS ORDERBYCOLUMN,
MOC_ITEMS_1.MOCITEMID,
MOC_ITEMS_1.SENDERPARTYID,
MOC_ITEMS_1.RECIPIENTPARTYID,
...
ORDER BY 1

Things that I can think of that could cause unexpected sorting, is at least COLLATION, certain collations sort things differently than others. If you get expected sorting if using ORDER BY MOC_ITEMS_1.MOCITEMID rather than CASE, then the reason is something else.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of d_dude_2003
Sent: 15. januar 2008 09:53
To: firebird-support@yahoogroups.com
Subject: [firebird-support] problem with ORDER BY

Hi there folks,

I have the following SQL

SELECT
MOC_ITEMS_1.MOCITEMID,
MOC_ITEMS_1.SENDERPARTYID,
MOC_ITEMS_1.RECIPIENTPARTYID,
MOC_CATEGORIES_1.CATEGORY,
MOC_SUBCATEGORIES_1.SUBCATEGORY,
STATUSES_1.NAME AS STATUS,
PRIORITIES_1.NAME AS PRIORITY,
STATUSES_2.NAME AS ACCESSSTATUS,
PARTIES_1.NAME AS SENDERPARTY,
EMPLOYEES.FULLNAMEE AS SENDERPERSON,
UARCUSERS_1.USERNAME AS CURRENTOWNER,
PARTIES_2.NAME AS RECIPIENTPARTY,
PARTYEMPLOYEES_2.NAME AS RECIPIENTPERSON,
MOC_ITEMS_1.CORRESPONDENCETYPE,
MOC_ITEMS_1.DATEOFCORRESPONDENCE,
MOC_ITEMS_1.DATERECEIVED,
MOC_ITEMS_1.DATEENTERED,
MOC_ITEMS_1.ENTEREDBY,
MOC_ITEMS_1.KEYWORDS,
MOC_ITEMS_1.LETTERNUMBER,
MOC_ITEMS_1.REFERREDNUMBER,
MOC_ITEMS_1.SUBJECT, MOC_ITEMS_1.COPIED,
MOC_ITEMS_1.INVOICENUMBER,
MOC_ITEMS_1.PONUMBER,
MOC_ITEMS_1.PACKAGECONTENTS,
MOC_ITEMS_1.INVOICEDUEDATE,
MOC_ITEMS_1.PROPOSEDVOLUMENUMBER,
MOC_ITEMS_1.VOLUMENUMBER,
MOC_ITEMS_1.FILINGSTATUS,
MOC_ITEMS_1.ACCEPTED,
MOC_ITEMS_1.ATTACHMENT, MOC_ITEMS_1.DRAFT,
MOC_ITEMS_1.DRAFTFINALIZED
FROM MOC_ITEMS MOC_ITEMS_1 INNER JOIN MOC_CATEGORIES
MOC_CATEGORIES_1 ON (MOC_CATEGORIES_1.MOCCATEGORYID =
MOC_ITEMS_1.MOCCATEGORYID)
INNER JOIN MOC_SUBCATEGORIES MOC_SUBCATEGORIES_1 ON
(MOC_SUBCATEGORIES_1.MOCSUBCATEGORYID = MOC_ITEMS_1.MOCSUBCATEGORYID)
INNER JOIN STATUSES STATUSES_1 ON (STATUSES_1.STATUSID =
MOC_ITEMS_1.STATUSID)
INNER JOIN PRIORITIES PRIORITIES_1 ON (PRIORITIES_1.PRIORITYID
= MOC_ITEMS_1.PRIORITYID)
INNER JOIN STATUSES STATUSES_2 ON (STATUSES_2.STATUSID =
MOC_ITEMS_1.ACCESSSTATUSID)
INNER JOIN PARTIES PARTIES_1 ON (PARTIES_1.PARTYID =
MOC_ITEMS_1.SENDERPARTYID)
INNER JOIN PARTIES PARTIES_2 ON (PARTIES_2.PARTYID =
MOC_ITEMS_1.RECIPIENTPARTYID)
INNER JOIN EMPLOYEES ON (EMPLOYEES.EMPLOYEEID =
MOC_ITEMS_1.SENDERPERSONID)
INNER JOIN PARTYEMPLOYEES PARTYEMPLOYEES_2 ON
(PARTYEMPLOYEES_2.OFFICEEMPLOYEEID = MOC_ITEMS_1.RECIPIENTPERSONID)
INNER JOIN UARCUSERS UARCUSERS_1 ON (UARCUSERS_1.USERID =
MOC_ITEMS_1.CURRENTOWNER)
WHERE (MOC_ITEMS_1.CORRESPONDENCETYPE = 'Outgoing') AND
(MOC_ITEMS_1.DATERECEIVED = '1/2/2006')
ORDER BY

CASE :OrderBy

WHEN 0 THEN MOC_ITEMS_1.MOCITEMID

WHEN 1 THEN MOC_ITEMS_1.MOCCATEGORYID

WHEN 2 THEN MOC_ITEMS_1.LETTERNUMBER

WHEN 3 THEN MOC_ITEMS_1.SENDERPARTYID

WHEN 4 THEN MOC_ITEMS_1.RECIPIENTPARTYID

WHEN 5 THEN MOC_ITEMS_1.CURRENTOWNER

WHEN 6 THEN MOC_ITEMS_1.REFERREDNUMBER

END

The problem is, when i pass the OrderBy value 0, it wont sort by
MOCITEMID. Also sometimes it doesnt sort by other fields as well.
FireBird version is 1.5 and i am running the query in Database Workbench.

Are there known issues with passing parameters in ORDER BY clause?

Thanx alot.