Problem with order by clause

From: Phil Huber <phil_at_mtu.edu>
Date: Tue, 15 Aug 2000 15:08:00 -0400
Message-ID: <39999510.2A0D8946@mtu.edu>

Oracle supports a subquery (or a function) within an "order by" clause. However, the descending keyword does not work when the sort parameters are selected as a subquery:

Parameter table:

SOR SORT_FIELD

---      ------------------------------
01A    spriden_activity_date asc
01D    spriden_activity_date desc
02A    spriden_last_name asc
02D    spriden_last_name desc

03A spriden_first_name asc
03D spriden_first_name desc

Query:
select spriden_last_name, spriden_first_name, spriden_activity_date from saturn.spriden
where spriden_last_name like 'Ke%'
order by (select sort_field from sort_parms where sort_code = '&sort_code');

Descending sorts do not work. The behavior is the same under Oracle 8.1.5 and under Oracle 7.3.4. Is this a known Oracle bug?

Descending sorts work when the order by specification is hard coded in its entirety.
I also tried hardcoding just the "desc" option after the subquery, and still did not get a descending sort.

This could be a problem during the parse phase where the query is not being expanded properly, and as such the execution plan being generated is not correct.

Please advise.

--


  +----------------------------------------------------------------+

    Phillip Huber    Data Base Administrator    phil_at_mtu.edu


  +----------------------------------------------------------------+

Received on Tue Aug 15 2000 - 14:08:00 CDT