At 07:53 PM 15/01/2008, you wrote:

>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?

Certainly! :-) ORDER BY and GROUP BY are structural elements in an SQL query specification, so you cannot parameterize them or make them conditional. You use parameters to provide values for search keys in WHERE clauses and input arguments for PSQL blocks or modules.

./heLen