Oracle rwbuilder 12c Order By Using Case Statement
I have a report that contains a query that I want to sort the results of by a specific column dynamically. I have a user parameter being passed from my form to define what column to order by. The user parameter is ":SORTBY" and the CASE statement for the ORDER BY clause is as follows;
ORDER BY
OFFICE_NAME,
(CASE
WHEN :SORTBY = 'MF' THEN NAME
WHEN :SORTBY = 'MD' THEN MODEL
WHEN :SORTBY = 'BC' THEN BARCODE
WHEN :SORTBY = 'AD' THEN TO_CHAR(DATE_IN_SERVICE, 'MMDDRR')
END) ASC
This works as expected when running the query alone in TOAD and it also works in the Reports Builder developer tool but does not work when I run it from my reports server. I have tried variations of the order by statement using the column number instead of the name and have even tried using the DECODE function instead of CASE. Everything works fine in TOAD and the Developer but does not work on the server.