Thanks for the question, Per.

Asked: February 09, 2004 - 9:40 pm UTC

Last updated: November 30, 2012 - 3:43 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

I need guidance as to the order by clause for a hard refcursor
Unless I hardcode the order by clause the order by gets ignored when fetching from the cursor.
Can I achieve dynamic ordering on ver 8.1.7 or any higher version ?
I can use parameters in all other places I've tried except the order by

sample code

open p_cursor for
select
e.code
, e.name
from events e
where e.sys_value = c_rv
order by e.name asc; -- ok
order by l_sortitem ; -- gets ignored l_sortitem := ' e.code asc'
order by decode( l_sort_by, '12',' e.name asc', ' e.code asc'); -- gets ignored

Thanks for any suggestions

and Tom said...

the order by decode DOES NOT GET IGNORED.

select * from t order by ' $.%@@#$ @!#';

that is what you are doing in effect -- ordering by a constant. so-- not ignored, just not what you wanted to do.

Two methods:

a) order by decode( l_sort_by, '12', e.name, to_char( code, '0000000000009' ) )

that is, return a character string to sort by. if sortby = 12, return ename to sort. else return code (which I assume is a number perhaps) in a fixed width field so it sorts. if you had a date, you would use to_char( dt, 'yyyymmddhh24miss' ).

if you have numbers that go negative, you'll need to find a format that "sorts" right.

b) use dynamic sql or an if then opened cursor:

is
type rc is ref cursor;
l_cursor rc;
begin
if l_sort_by = '12'
then
open l_cursor for select ..... order by e.aname asc;
else
open l_cursor for select ..... order by e.code asc;
end if;
loop
fetch l_cursor into ....;
exit when l_cursor%notfound;

OR

begin
if (l_sort_by = '12') then
l_query := 'select ..... order by e.name asc';
.....

open l_cursor for L_QUERY;
....

Rating

(15 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Per Elmgreen, February 10, 2004 - 5:30 pm UTC

Great - I'm getting the sorting I wanted - had overlooked the fixed width issue

Translate statement from a) to b)

A reader, February 10, 2004 - 7:53 pm UTC

I cannot set asc/desc dynamically using a)

Would anyone have a utility function to convert a statement from from a to b ie to build up string l_query and but in all the ||, ' and ''' in the right places

February 11, 2004 - 9:03 am UTC

sure you can.

order by
decode( l_asc_desc, 'ASC',
decode( l_sort_by, '12', e.name, to_char( code, '0000000000009' ) ) ),
decode( l_asc_desc, 'DESC',
decode( l_sort_by, '12', e.name, to_char( code, '0000000000009' ) ) ) DESC

if you use asc, and l_sort_by was 12, that would be like:

order by e.name, NULL desc

if you use DESC, and l_sort_by was 12, that would be like

order by NULL, e.name desc

how in the world could someone have a utility to convert your unique queries? AND if there was -- it would mean that you already figured out how to do this in a)??

Gotcha

A reader, February 11, 2004 - 5:35 pm UTC

Very useful approach - nesting of decode

A reader, August 26, 2004 - 12:05 pm UTC

Dynamic Order By

Ed Girard, October 16, 2008 - 5:13 pm UTC

I'm attempting to perform a dynamic ORDER BY clause with DECODE as you mentioned above. However, it doesn't seem to work if the column is a VARCHAR2. Otherwise, it does.

What am I missing?

create table order_test
(
  order_id  number,
  name      varchar2(20),
  price     number
);


insert into order_test
   (order_id, name, price)
 values
   (1, 'banana', 44);
insert into order_test
   (order_id, name, price)
 values
   (2, 'apple', 11);
insert into order_test
   (order_id, name, price)
 values
   (3, 'grape', 22);
insert into order_test
   (order_id, name, price)
 values
   (4, 'orange', 33);
commit;

Now, how come the 1st and 3rd queries work and the 2nd one doesn't. The 2nd one returns ORA-01722 when attempting to sort a VARCHAR2 column (which is always the case).

cis2@pkdev> SELECT ot.order_id,
  2         ot.name,
  3         ot.price
  4  FROM   order_test ot
  5  ORDER BY DECODE(1, 1, ot.order_id, 2, ot.name, 3, ot.price);

  ORDER_ID NAME            PRICE
---------- ---------- ----------
         1 BANANA             44
         2 APPLE              11
         3 GRAPE              22
         4 ORANGE             33


cis2@pkdev> SELECT ot.order_id,
  2         ot.name,
  3         ot.price
  4  FROM   order_test ot
  5  ORDER BY DECODE(2, 1, ot.order_id, 2, ot.name, 3, ot.price);
ORDER BY DECODE(2, 1, ot.order_id, 2, ot.name, 3, ot.price)
                                      *
ERROR at line 5:
ORA-01722: invalid number


cis2@pkdev> SELECT ot.order_id,
  2         ot.name,
  3         ot.price
  4  FROM   order_test ot
  5  ORDER BY DECODE(3, 1, ot.order_id, 2, ot.name, 3, ot.price);

  ORDER_ID NAME            PRICE
---------- ---------- ----------
         2 APPLE              11
         3 GRAPE              22
         4 ORANGE             33
         1 BANANA             44

I'm on 10g - What's wrong here?

Thanks for your assistance.

October 17, 2008 - 9:15 pm UTC

decode defaults the return value to be the FIRST returned value - you return a number first, so decode says "I'll be returning NUMBERS!!!!"

and cannot convert apple into a number.

So, either coerce everything to a string (as demonstrated) or use the superior (but later demonstrated) method:

order by
ORDER BY
DECODE( plsql_variable, 1, ot.order_id )
decode( plsql_variable, 2, ot.name)
decode( plsql_variable, 3, ot.price);

which, if plsql_variable = 2, would be like:

order by NULL, ot.name, NULL;

and don't worry about the type conversions at all.

Dynamic Order By

Ed Girard, October 20, 2008 - 4:12 pm UTC

No big deal but you mistakenly forgot the commas at the end of each statement but yes, this is exactly what I was looking for.

Thank you so much

SQL> SELECT ot.order_id,
  2         ot.name,
  3         ot.price
  4  FROM   order_test ot
  5  ORDER BY DECODE(2, 1, ot.order_id),
  6           DECODE(2, 2, ot.name),
  7           DECODE(2, 3, ot.price);

  ORDER_ID NAME            PRICE
---------- ---------- ----------
         2 APPLE              11
         1 BANANA             44
         3 GRAPE              22
         4 ORANGE             33

The above "2" in the decode would be a parameter in PL/SQL.

A reader, September 23, 2009 - 9:58 pm UTC

if the table we are doing the order on has a 100 columns say any of which we may want to order by, is there a more generic solution then a decode for each column?

September 28, 2009 - 2:31 pm UTC

nope, you would probably be looking to do dynamic sql where you carefully (considering sql injection issues) append the column you want to order by.

order by

marc vafaie, March 29, 2012 - 9:23 am UTC

Hi,
I am having similar issues i have variable p_column with can take the value 'timestamp asc' or timestamp desc i.e any of the select column in either asc or desc etc..)

SELECT elogbookno,rejection_desc,rejection_reason,timestamp, FROM table a
WHERE timestamp >= p_date_from
AND timestamp <= p_date_to
ORDER BY decode(p_column,'TIMESTAMP desc', 'timestamp desc',1)

I thought it would be a simple case of checking the p_column variable using the decode and if the string matches then set the order by text to its equivalent else just order by the first parameter...any ideas how i can get this working properly as when i run the query it doesnt order correctly.
thanks

March 29, 2012 - 10:19 am UTC

umm, did you read the original answer?

I say that because the original question is identical to yours.

Dynamic ASC/DESC

Ahmad Al-Sallal, April 26, 2012 - 3:40 am UTC

Hi Tom,
About ordering, why ORACLE doesn't provide an easy way for to construct our Order by clause, i have 2 senarios,
1) need to order the columns depending on there position "without using dunamic SQL of course", but it take it as numbers
>> select * from emp
    order by decode ( 1 , 1 , 1 )
Yes yes i know it's the same as the first question :) , but i need to find an easy way to modify all my reports queries!!!!
2) How can we specify ASC or DESC via a variable, i tried two ways neither were work (again no dynamic SQL :) )
>> select * from emp
    order by decode ( 1 , 1 , ename asc , ename desc )
>> select * from emp
    order by ename decode (1,1,asc,desc)

April 26, 2012 - 8:13 am UTC

I don't know what you mean with:

order by decode( 1, 1, 1 )

since that is the same as:

order by 'hello world'

?????

please define what you mean by "need to order columns depending on their positition"

If you are asking the original question again, I've answered that - once with static sql and once with dynamic sql.

If you wanted to

select * from emp order by <any of empno, ename, hiredate>

the query could be:

select * 
  from emp 
 order by decode( :some_input, 
                  'ENAME', ename, 
                  'HIREDATE', to_char( hiredate, 'yyyymmddhh24miss'), 
                  'EMPNO', to_char( empno, '00000000000' ) );

All you have to do now is pass in "some input" to tell us what to order by.

(although please bear in mind that the dynamic sql approach - where the optimizer KNOWS what to order by - will almost certainly be much more performant)

2) you would/could:


select * 
  from emp 
 order by case when :some_input = 'ASC' then ename end ASC,
          case when :some_input = 'DESC' then ename end DESC

that will either order by ename asc or ename desc

Conditional Order By.

Vipul Garg, August 31, 2012 - 6:44 am UTC

Very useful information. My problem quickly solved.

As usual excellent solution by Tom.

Order by on multiple columns

rebisco, October 18, 2012 - 5:54 am UTC

Hi Tom,

Is this applicable to multiple columns?
What if I want to ORDER BY COLUMN1, COLUMN2,... What should I do?

Using the data posted by Ed Girard above, If I want the result to be ordered by NAME then PRICE, how can I do that?

Thanks,
rebisco

October 18, 2012 - 8:25 am UTC

order by ORDER BY
DECODE( plsql_variable1, 1, ot.order_id )
decode( plsql_variable1, 2, ot.name)
decode( plsql_variable1, 3, ot.price),
DECODE( plsql_variable2, 1, ot.order_id )
decode( plsql_variable2, 2, ot.name)
decode( plsql_variable2, 3, ot.price)
;

you would just have two variables - one says what to order by first, the other says what to order by second.

Typo may?

rebisco, October 18, 2012 - 8:35 pm UTC

Hi Tom,

Am I seeing typo in there?
I tried using the one you posted but gives me error.
By the way, I'm not sure if this is a good idea of how to construct a dynamic ORDER BY clause. See, if my query needs to be order in 5 or 7 columns, do I need to create 5 or 7 variables too? Other than using a ref cursor to build a dynamic SQL, is there any other way to workaround with this?
My requirement is to build a query which will be used inside a for..loop, is it possible to execute like this in oracle?

DECLARE
  vSQL VARCHAR2(2000);
  vSortSeq NUMBER := 1; --This can be 1-5
  vOrder VARCHAR2(200);
BEGIN
  vSQL := 'SELECT col1, col2, col3, col4, col5 from TABLE1';
  
  IF vSortSeq = 1 THEN
    vOrder := 'col1,col2,col3,col4,col5';
  ELSIF vSortSeq = 2 THEN
    vOrder := 'col2,col1,col3,col4,col5';
  ELSIF vSortSeq = 3 THEN
    vOrder := 'col3,col1,col2,col4,col5';
  ELSIF vSortSeq = 4 THEN
    vOrder := 'col4,col1,col2,col3,col5';
  ELSIF vSortSeq = 5 THEN
    vOrder := 'col5,col1,col2,col3,col4';
  END IF;

  FOR x in vSQL || vOrder
  LOOP
    <statement>
    <statement>
  END LOOP;
END;

Thanks,
rebisco

October 23, 2012 - 10:59 am UTC

See, if my query needs to be order in 5 or 7 columns, do I need to create 5 or 7 variables too?

probably not for 5 or 7 columns - then you would probably want to use an order by list in dynamic sql - assuming you don't actually generate every combination of course!

you would have to use a ref cursor (or a dbms_sql cursor but that would be harder).

see option (b) in the original answer.

Sort/Order

karma, November 05, 2012 - 1:04 pm UTC

Is there a way of ordering resultset based on search string in a particular order. For example if user types in search string '500' then the resultset needs to be ordered as follows:

50001
50002
50003
35001
30500

On top of the resultset it should have exact string match, followed by First 'n' match and then anywhere in the string. So the order would be
1) Eaxct Match
2) First 'n' characters match
3) Anywhere in the string

November 05, 2012 - 1:24 pm UTC

order by instr( searched_str, searched_for_str ), searched_str;

Return different data types from a decode in order by

Rak, November 29, 2012 - 6:16 am UTC

Hi Tom,

I have a requirement where i need to return different data types from a decode statement (that i am using in the order by clause).

SELECT LAST_NAME,
RESERVE_BEGIN_DATE,
INSERT_DATE,
ORDER_ID
FROM FROM ABCD
ORDER BY DECODE(:IN_SORTED_BY_STRING,
'LN_AD',
LAST_NAME|| ',' || RESERVE_BEGIN_DATE,
'AD_LN',
RESERVE_BEGIN_DATE || ',' || LAST_NAME,
'TD_LN ',
INSERT_DATE || ',' || LAST_NAME,
'LN_TD',
LAST_NAME|| ',' || INSERT_DATE,
'OR_ID',
ORDER_ID);
Order by Date is not happening here as decode is returning String values.

How can i re-write the order by clause so that, based on my input param :IN_SORTED_BY_STRING, i will order by LAST_NAME,
RESERVE_BEGIN_DATE, ORDER_ID.

Thanks,
Rak

November 30, 2012 - 3:43 am UTC

I have a requirement where i need to return different data types from a decode
statement

you shall have to change your requirement then - since that is not possible.

and if you sort by the string:

LAST_NAME|| ',' || RESERVE_BEGIN_DATE

that won't sort properly - think about why it wouldn't....

It seems you might be best served by using dynamic SQL here - using a different order by OR - by encoding the data in a string so that it all sorts properly.

eg:

order by decode( :x, 
'LN_AD', rpad( nvl( last_name, ' ' ), 30 ) || 
         nvl( to_char(reserve_begin_date, 'yyyymmddhh24miss' ), '00000000000000' ),
'AD_LN', nvl( to_char(reserve_begin_date, 'yyyymmddhh24miss' ), '00000000000000' ) ||
         rpad( nvl( last_name, ' ' ), 30 ), 
.....

or, you can use a bigger order by:

order by decode( :x, 'LN_AD', last_name ), decode( :x, 'LN_AD', reserve_begin_date ),
         decode( :x, 'AD_LN', reserve_begin_date ), decode( :x, 'AD_LN', last_name ),
         decode( :x, 'TD_LN', insert_date ), decode( :x, 'TD_LN', last_name ),
         decode( :x, 'LN_TD', last_name ), decode( :x, 'LN_TD', insert_date ),
         decode( :x, 'OR_ID', order_id );

Thanks

Rak, November 30, 2012 - 4:29 am UTC

Thanks tom. Appreciate it.

Live SQL

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Dev Gym

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Thanks

Translate statement from a) to b)

Gotcha

Dynamic Order By

Dynamic Order By

order by

Dynamic ASC/DESC

Conditional Order By.

Order by on multiple columns

Typo may?

Sort/Order

Return different data types from a decode in order by

Thanks

More to Explore

Live SQL

Dev Gym

Provide dynamic order by for "hard" cursor

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Thanks

Translate statement from a) to b)

Gotcha

Dynamic Order By

Dynamic Order By

order by

Dynamic ASC/DESC

Conditional Order By.

Order by on multiple columns

Typo may?

Sort/Order

Return different data types from a decode in order by

Thanks

More to Explore

Live SQL

Dev Gym