RE: using variable name in order by
- From: "Justin Mitchell" <jmitchell@xxxxxxxxxxx>
- To: <geraldine_2@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 23 Mar 2005 09:47:51 -0500
If you want to use a variable in the order by clause without using
dynamic SQL, why not use an expression? =20
open emprefcursor for
select name=20
from emp=20
where sal > 10000=20
order by case varname when 'SAL' then sal=20
when 'DEPNO' then depno
else empno end;
Now granted, the columns that you use have to be of the same type, or
have to be converted using TO_CHAR or whatever's appropriate, but this
method generally works decently for us in several cases. In the tracing
I've done on it I haven't noticed anything major being "wasted". As
always, I can see the efficiency depending upon the table and data
structure, so YMMV.
Thanks,
Justin Mitchell
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
geraldine_2@xxxxxxxxxxx
Sent: Tuesday, March 22, 2005 3:16 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: using variable name in order by
Hi,
Is there a limitation whereby I can't substitute the variable (varname)
with literal string such such name or sal in the ORDER BY clause below?
I can use variable in the WHERE clause but not in the ORDER BY.=20
OPEN emprefcursor FOR
select name from emp where sal>10000 order by varname;
Based on certain conditions, the varname can be name or sal. However,
for some reasons, when I ran the procedure, it failed to replace the
varname. Any other alternatives to accomplish the same thing?
Thanks.
Geraldine
--
//www.freelists.org/webpage/oracle-l
The information contained in this electronic message from Universal Tax
Systems, Inc., and any attachments, contains information that may be
confidential and/or privileged. If you are not the intended recipient, you are
hereby notified that any disclosure, copying, distribution or use of this
information is strictly prohibited. If you have received this communication in
error, please notify Universal Tax Systems, Inc., immediately by e-mail or by
telephone at 706/290-7200, and destroy this communication. Thank you.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
