Re: dynamic ORDER BY values

Hey thanks a lot! That worked. DECODE never even entered my mind as a solution for this problem.

I might be stating the obvious, but one thing I'll mention for posterity is that all the resulting ORDER BY possibilities from the DECODE need to be the same datatype, so you might need to convert a few things with TO_CHAR or what-have-you.

Also, I tried doing this with column indexes and the entire ORDER BY clause was ignored. For example, this didnt' work:

ORDER BY
       DECODE(p_sort, 1, 1, 2, 2, 3, 3, 4, 4, 1);

In article <wz2Z5.30481$58.5144410_at_typhoon.tampabay.rr.com>,   "John Alexander" <jalexander_at_summitsoftwaredesign.com> wrote:
> 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.
> >
>
>

Sent via Deja.com
http://www.deja.com/ Received on Mon Dec 18 2000 - 20:25:29 CST