Re: dynamic ORDER BY values

Is it dynamic SQL?

If so, then
'Select a,b,c,d from table where qualifier=:fred order by 1,2,3' can be built as
'Select a,b,c,d from table where qualifier=:fred order by 2,3,1'

in the command string.

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.
> >
>
>

--
Joseph R.P. Maloney, CCP,CSP,CDP
MPiR, Inc.
502-451-7404
some witty phrase goes here, I think.


Sent via Deja.com http://www.deja.com/
Before you buy.