Re: dynamic ORDER BY values

From: John Alexander <jalexander_at_summitsoftwaredesign.com>
Date: Mon, 11 Dec 2000 11:07:08 GMT
Message-ID: <wz2Z5.30481$58.5144410@typhoon.tampabay.rr.com>

One way (probably the simplest) to change the sort order is with decode:

          SELECT
              u.last_name,
              u.first_name,
              u.company_name
          FROM
              user u
          ORDER BY

DECODE(p_sortOrder,'LAST',u.last_name,'FIRST',u.first_name,u.company_name)

John Alexander
St. Petersburg, FL
www.SummitSoftwareDesign.com

<cpfrommer_at_my-deja.com> wrote in message news:911mcq$h6k$1_at_nnrp1.deja.com...
> Hi all. I'm wondering if anyone knows a way to
> dynamically ORDER BY different columns in a SQL
> query within a PL/SQL function.
>
> I have a function that I am calling from Java,
> basically it just returns a CURSOR from a query.
> I want to be able to specify which column to sort
> by on the Java side.
>
> Is there any way to dynamically ORDER BY
> different columns? Here's the very basics of
> what I'm trying to do:
>
> ==============================================
> FUNCTION function_name(p_sortOrder IN NUMBER)
> RETURN REF CURSOR
> IS
> results REF CURSOR;
> BEGIN
> OPEN results FOR
> SELECT
> u.last_name,
> u.first_name,
> u.company_name
> FROM
> user u
> ORDER BY
> p_sortOrder;
>
> return results;
>
> END function_name;
> ============================================
>
> My apologies if the syntax isn't perfect, but you
> get the idea.
>
> I looked into possibly using the DBMS_SQL package
> to parse and bind it as a variable, but I'm not
> sure if that will work and it also seems like a
> bit of overkill (performance hit as well?).
>
> Any help would be greatly appreciated...
>
> Chad
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Received on Mon Dec 11 2000 - 05:07:08 CST