How can I order my query results dynamically in Oracle?

SELECT   Tbl.*,开发者_StackOverflow
          ROWNUM RN1
        FROM
          (
            SELECT DISTINCT( LEAVEID ),
                LEAVECODE,
                LEAVENAME,
                DESCRIPTION,
                STATUS
             FROM HRM_LEAVECONFIGURATION
              WHERE
                (
                  (
                    :LEAVENAME IS NULL OR
                    UPPER( LEAVENAME ) LIKE UPPER( :LEAVENAME )
                  )
                  AND
                  (
                    :STATUS IS NULL OR
                    STATUS   = :STATUS
                  )
                  AND
                  (
                    ISFIXED <> 1 OR
                    ISFIXED IS NULL
                  )
                  AND
                  (
                    :LEVECODE         IS NULL OR
                    UPPER( LEAVECODE ) = UPPER( :LEVECODE )
                  )
                )
              ORDER BY(
                  CASE
                      (
                        SELECT   t.data_type
                          FROM user_tab_columns t
                          WHERE t.TABLE_NAME = 'HRM_LEAVECONFIGURATION' AND
                            t.COLUMN_NAME    = 'LEAVENAME'
                      )
                    WHEN 'VARCHAR2'
                    THEN 'UPPER(LEAVENAME)'
                    ELSE 'LEAVENAME'
                  END ) DESC
          )
          Tbl

This is the code I am having problem.

Here I am passing a field name as parameter, according to the data type of the field I want give uppercase function.

e.g.,If I give leavename as the column to be ordered, if the datatype of leavename is varchar2 then order by upper(leavename) Descending, if datatype of leavename is other than varchar then order by just leavename.

Here my problem is this query is working but it it s not getting ordered. Is there any other suggestion ?

I'm not sure under what circumstances the datatype of a column will vary. Surely LEAVENAME is always going to be a VARCHAR2?

So all you need is this:

  ORDER BY(
  CASE
      when :LEAVENAME is not null THEN UPPER(LEAVENAME)
    ELSE LEAVENAME
  END ) DESC

Perhaps your precise business logic is different and you want to order by whatever columns for which you actually pass values. In which case you should write something like this:

  ORDER BY(
  CASE
      when :LEAVENAME is not null THEN UPPER(LEAVENAME)
      when :LEVECODE is not null THEN UPPER(LEVECODE)
    ELSE LEAVENAME
  END ) DESC

Incidentally, you need to be careful with such queries. They confuse the optimizer, which means they may perform poorly some or indeed all of the time. Assembling a query dynamically is often a better approach.

I am pretty sure, you can't bind column names.

The order by belongs one layer out. You are sorting the result set that you are selecting from, but not the final set. The order by belongs after the final Tbl at the end of the query.