Re: Order By problem with Dates

Whoops -

    minus several million for not reading the question properly.     still I seem to be in good company on this one.

I missed the distinct() the first time around Since your code is only reformatting the values, and not having any 'arithmetical' effect on them, I suggest using an in-line view.

select your_complicated_expression_without_the_distinct from (

    select distinct s_date
    from tssds.ehefmsam
    where locdesc = 'MW-8'
)
order by s_date

This will also be more efficient than the original since it only does the complicated to_char, to_date stuff once per distinct date.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

rock_cogar_at_my-deja.com wrote in message <7pe8b3$pod$1_at_nnrp1.deja.com>...
>I wish it was that easy. On Oracle 8.1.5 on NT 4.0 sp5, this is what I
>get from SQL Worksheet (1.3.5):
>
>SQLWKS> select distinct to_char(to_date(substr(to_char(s_date),1,4)||'-
>'||substr(to_char(s_date),5,2)||'-'||substr(to_char(s_date),7,2),'YYYY-
>MM-DD'),'Mon DD, YYYY')
> 2> from tssds.ehefmsam
> 3> where locdesc = 'MW-8'
> 4> order by S_DATE asc;
>order by S_DATE asc
> *
>ORA-01791: not a SELECTed expression
>
Received on Wed Aug 18 1999 - 07:42:04 CDT