RE: using variable name in order by

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of geraldine_2_at_comcast.net
Sent: Tuesday, March 22, 2005 3:16 PM
To: oracle-l_at_freelists.org
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

--

http://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.

--

http://www.freelists.org/webpage/oracle-l Received on Wed Mar 23 2005 - 09:51:46 CST