Oracle Reports (MOSC)

MOSC Banner

Oracle rwbuilder 12c Order By Using Case Statement

edited Apr 26, 2018 5:05AM in Oracle Reports (MOSC) 2 commentsAnswered

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.

Tagged:

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center