Oracle Dynamic Desc and Asc in Order By

Oracle dynamic DESC and ASC in order by

If you also want to make the sort order (ASC/DESC) dynamic, you could do the following:

SELECT ...
Order By CASE WHEN InputParam = 'PRICE' THEN l_so * OFFER_PRICE END,
         CASE WHEN InputParam = 'ENDING SOON' 
              THEN l_so * (SYSDATE - EXPIRY_DATE) END, 
         CASE WHEN InputParam = 'DISCOUNT' THEN l_so * DISC_PERCENTAGE END,
         CASE WHEN InputParam = 'SAVING' THEN l_so * SAVING END

with a variable l_so that contains 1 or -1 depending upon which sort order you want.

SQL Dynamic ASC and DESC

You can do solutions like @TonyAndrews by manipulating numeric or data values. For VARCHAR2 an alternative to dynamic SQL could be to have two expressions:

order by
   case when :sorting='ASC' then col1 end ASC,
   case when :sorting='DESC' then col1 end DESC

When :sorting has the value 'ASC' the result of that ORDER BY becomes like if it had been:

order by
   col1 ASC,
   NULL DESC

When :sorting has the value 'DESC' the result of that ORDER BY becomes like if it had been:

order by
   NULL ASC,
   col1 DESC

One downside to this method is that those cases where the optimizer can skip a SORT operation because there is an index involved that makes the data already sorted like desired, that will not happen when using the CASE method like this. This will mandate a sorting operation no matter what.

How to specify dynamic order by clause in Oracle

Order by two outer CASE expressions, one ASC one DESC. In the first one check if you want to sort ASC and if not let the expression return a constant or NULL, i.e. it doesn't change the order. For the case you want to sort ASC add an inner CASE expression, that returns the column to sort by. Analogously handle the DESC case.

...
ORDER BY CASE
           WHEN in_order_dir = 'ASC' THEN
             CASE
               WHEN in_order_by = 1 THEN
                 city_code
               WHEN in_order_by = 2 THEN
                 city_name
               ...
             END
         END ASC,
         CASE
           WHEN in_order_dir = 'DESC' THEN
             CASE
               WHEN in_order_by = 1 THEN
                 city_code
               WHEN in_order_by = 2 THEN
                 city_name
               ...
             END
         END DESC;

Maybe you have to adapt it because of type incompatibilities between the columns, I can't tell that from what you posted. But it should convey the general concept.

---

Edit:

On the type problem:

One possibility is to convert the columns to compatible data types in a way the order is kept. For instance, if you have a char_column (of the type char), a date_column and an integer_column (of date and integer) you could do

to_char(date_column, 'YYYYMMDDHH24MISS')

to convert the date and

lpad(11, 38, '0')

to convert the integer to char.

The cleaner thing to do, but the one with more work to do (for the programmer, in terms of performance there should be no significant difference whatsoever) is to split the expressions once more. I.e. have an outer CASE for each type your columns are for each direction.

Like

ORDER BY CASE
           WHEN in_order_dir = 'ASC' THEN
             CASE
               WHEN in_order_by = 1 THEN
                 char_column1
               WHEN in_order_by = 2 THEN
                 char_column2
               ...
             END
         END ASC,
         CASE
           WHEN in_order_dir = 'ASC' THEN
             CASE
               WHEN in_order_by = 3 THEN
                 date_column1
               WHEN in_order_by = 4 THEN
                 date_column2
               ...
             END
         END ASC,

and so on for all types and analogously for DESC. Remember, if the column isn't meant to be sorted after such CASE will yield NULL and sorting by that won't influence the order.

How to generate Dynamic Order by clause in PL/SQL procedure?

When doing a dynamic sort, I recommend using separate clauses:

order by (case when in_sort_column = 'col1' then col1 end),
         (case when in_sort_column = 'col2' then col2 end),
         (case when in_sort_column = 'col3' then col3 end)

This guarantees that you will not have an unexpected problem with type conversion, if the columns are of different types. Note that case return NULL without an else clause.

Oracle ORDER BY input value

The Link provided by @user7294900 contains the solution for your question.
Basically, all you have to do is implement the CASE section correctly.

SELECT * FROM your_table
WHERE SOMETHING = 'something'
ORDER BY
CASE WHEN INPUT = 'str1' THEN 1 END,
CASE WHEN INPUT = 'str2' THEN 2 END DESC;

Dynamic order by date data type in Oracle using CASE

Should be simple - just use ISO date format in your case:

TO_CHAR(IR.IDATE, 'yyyy-mm-dd')

and you should be fine.

SQL order by DECODE and CASE return different data for the same conditions

I asked what the datatype was because sorting looked like sorting strings, not numbers.

Have a look at this. First, only one expression in order by clause:

SQL> with test (seq_diff) as
  2    (select -990 from dual union all
  3     select -610 from dual union all
  4     select -1350 from dual union all
  5     select -1340 from dual
  6    )
  7  select *
  8  from test
  9  order by decode(seq_diff, abs(seq_diff), seq_diff, null);

  SEQ_DIFF
----------
      -990
     -1340
     -1350
      -610

SQL>

How are they sorted? They aren't. According to sample data set, no seq_diff is equal to abs(seq_diff) so ordering moves to null which causes "randomly" ordered values. They aren't sorted at all.

---

Now, let's add another decode into order by:

SQL> with test (seq_diff) as
  2    (select -990 from dual union all
  3     select -610 from dual union all
  4     select -1350 from dual union all
  5     select -1340 from dual
  6    )
  7  select *
  8  from test
  9  order by decode(seq_diff, abs(seq_diff), seq_diff, null),
 10           decode(seq_diff, abs(seq_diff), null, seq_diff) desc;

  SEQ_DIFF
----------
      -990
      -610
     -1350
     -1340

SQL>

The first decode didn't do anything, as if it doesn't exist so we move on to the second decode. Again, according to data set, no seq_diff is equal to abs(seq_diff), but this time it returns seq_diff. Documentation (as @krokodilko mentioned in their comment) says:

• The DECODE function returns a value that is the same datatype as the first result in the list.
• If the first result is NULL, then the return value is converted to VARCHAR2.
  
  • this is our case, so return value (seq_diff) is converted to varchar2
• If the first result has a datatype of CHAR, then the return value is converted to VARCHAR2.
• If no matches are found, the default value is returned.
• If default is omitted and no matches are found, then NULL is returned.

Once again: our case is the second one:

decode(seq_diff, abs(seq_diff), null, seq_diff)
                                ----
                                the first result is NULL

Therefore, seq_diff is converted to a string and values are sorted as such. Let's check that:

SQL> with test (seq_diff) as
  2    (select -990 from dual union all
  3     select -610 from dual union all
  4     select -1350 from dual union all
  5     select -1340 from dual
  6    )
  7  select *
  8  from test
  9  order by to_char(seq_diff) desc;

  SEQ_DIFF
----------
      -990
      -610
     -1350
     -1340

SQL>

See? The same result as we got with order by decode(seq_diff, abs(seq_diff), null, seq_diff) desc;

---

The final part of your order by clause is trivial (abs(dist_diff)), I guess there's no need to explain that.

---

That's why you got strange result with DECODE; actually, that's expected behavior.

Dynamic order direction

You could have two near-identical ORDER BY items, one ASC and one DESC, and extend your CASE statement to make one or other of them always equal a single value:

ORDER BY
      CASE WHEN @OrderDirection = 0 THEN 1
      ELSE
           CASE WHEN @OrderByColumn = 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
                WHEN @OrderByColumn = 'Visible' THEN CONVERT(varchar(2), Visible)
                WHEN @OrderByColumn = 'AddedBy' THEN AddedBy
                WHEN @OrderByColumn = 'Title' THEN Title
           END
      END ASC,
      CASE WHEN @OrderDirection = 1 THEN 1
      ELSE
           CASE WHEN @OrderByColumn = 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
                WHEN @OrderByColumn = 'Visible' THEN CONVERT(varchar(2), Visible)
                WHEN @OrderByColumn = 'AddedBy' THEN AddedBy           
                WHEN @OrderByColumn = 'Title' THEN Title
           END
      END DESC

---

Related Topics