I am usint 10g.

I know that you can put a decode in the order by section. But I tried
to see if it is possible to put a case statement in the order by
section. My little experiment did not work.

Has anyone in this forum successfully put a case statement in the
order by section? If so, how did you do it. Below is a sample of what
I tried to do.

CREATE OR REPLACE PROCEDURE TestOrderBy
(
User_Choice IN VARCHAR,
TestCur IN OUT Test_OrderBy.TEstCur )
IS

Begin

Open TestCur For
select object_name, User_Choice
from all_objects
Order by
Case
When User_Choice = 'A' Then object_name
Else Object_Name desc
End;

END TestOrderBy;

On 24 Jûl., 16:03, Rich <richma... (AT) earthlink (DOT) net> wrote:
You CAN put case statement as follows:

SQL> select city from table1
2 order by case when length(city) >8 then 1
3 when length(city) <5 then 2
4 else 3
5 end
6 /

CITY
----------
STOCKHOLM
RIGA
RIGA
RIGA
HELSINKI
HELSINKI
VILNIUS
TALLINN
TALLINN
TALLINN

But you are doing more you want query to be dynamic i.e. either order
it asc or desc based on user_choice. Your query doesn't make sense
because you are somehow trying to add asc or desc for each returned
row. So essentially you can simply add if condition and based on
user_choice return one or another cursor.

Gints Plivna
http://www.gplivna.eu

On 24 Jûl., 16:03, Rich <richma... (AT) earthlink (DOT) net> wrote:
You CAN put case statement as follows:

SQL> select city from table1
2 order by case when length(city) >8 then 1
3 when length(city) <5 then 2
4 else 3
5 end
6 /

CITY
----------
STOCKHOLM
RIGA
RIGA
RIGA
HELSINKI
HELSINKI
VILNIUS
TALLINN
TALLINN
TALLINN

But you are doing more you want query to be dynamic i.e. either order
it asc or desc based on user_choice. Your query doesn't make sense
because you are somehow trying to add asc or desc for each returned
row. So essentially you can simply add if condition and based on
user_choice return one or another cursor.

Gints Plivna
http://www.gplivna.eu

On 24 Jûl., 16:03, Rich <richma... (AT) earthlink (DOT) net> wrote:
You CAN put case statement as follows:

SQL> select city from table1
2 order by case when length(city) >8 then 1
3 when length(city) <5 then 2
4 else 3
5 end
6 /

CITY
----------
STOCKHOLM
RIGA
RIGA
RIGA
HELSINKI
HELSINKI
VILNIUS
TALLINN
TALLINN
TALLINN

But you are doing more you want query to be dynamic i.e. either order
it asc or desc based on user_choice. Your query doesn't make sense
because you are somehow trying to add asc or desc for each returned
row. So essentially you can simply add if condition and based on
user_choice return one or another cursor.

Gints Plivna
http://www.gplivna.eu

On 24 Jûl., 16:03, Rich <richma... (AT) earthlink (DOT) net> wrote:
You CAN put case statement as follows:

SQL> select city from table1
2 order by case when length(city) >8 then 1
3 when length(city) <5 then 2
4 else 3
5 end
6 /

CITY
----------
STOCKHOLM
RIGA
RIGA
RIGA
HELSINKI
HELSINKI
VILNIUS
TALLINN
TALLINN
TALLINN

But you are doing more you want query to be dynamic i.e. either order
it asc or desc based on user_choice. Your query doesn't make sense
because you are somehow trying to add asc or desc for each returned
row. So essentially you can simply add if condition and based on
user_choice return one or another cursor.

Gints Plivna
http://www.gplivna.eu