Re: Case statement in Order by section?

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Thu, 24 Jul 2008 07:18:06 -0700 (PDT)
Message-ID: <1011ba18-c7f0-44ae-9091-1581ad9b30db@p25g2000hsf.googlegroups.com>

On 24 Jûl., 16:03, Rich <richma..._at_earthlink.net> wrote:
> 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;

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 Received on Thu Jul 24 2008 - 09:18:06 CDT