Re: Order By problem with Dates

A copy of this was sent to rock_cogar_at_my-deja.com (if that email address didn't require changing) On Wed, 18 Aug 1999 12:18:52 GMT, you wrote:

>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
>
>Help !
>

Oh, I see what we overlooked -- the DISTINCT is getting in the way. When you through the distinct on there, s_date 'disappears' for the remaining query steps (the order by). only the column you selected is available for sorting. No problem -- this shows the above fails and then how to rewrite to make it work:

SQL> select distinct to_char(to_date(substr(to_char(s_date),1,4)||'-'||substr   2 (to_char(s_date),5,2)||'-'||
  3 substr(to_char(s_date),7,2),'YYYY-MM-DD'),'Mon DD, YYYY') x   4 from ehefmsam
  5 where locdesc = 'MW-8'
  6 order by s_date asc
  7 /
order by s_date asc

         *
ERROR at line 6:
ORA-01791: not a SELECTed expression

SQL>
SQL> select *
  2 from (
  3 select distinct to_char(to_date(substr(to_char(s_date),1,4)||'-'||substr   4 (to_char(s_date),5,2)||'-'||
  5 substr(to_char(s_date),7,2),'YYYY-MM-DD'),'Mon DD, YYYY') x   6 from ehefmsam
  7 where locdesc = 'MW-8'
  8 )
  9 order by to_date( x, 'Mon DD, YYYY' ) asc  10 /

X

Dec 03, 1991
Jan 21, 1992
Feb 27, 1992
Jun 17, 1992
Jul 17, 1992
Oct 16, 1992
Jan 26, 1995
Apr 22, 1995
Jul 16, 1995
Oct 17, 1995

10 rows selected.

>Rock Cogar.
>
>
>
>
>In article <934923181.4784.0.nnrp-03.9e984b29_at_news.demon.co.uk>,
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>>
>> You are allowed to 'order by' something that does not
>> appear in the select list, so you can do this:
>>
>> 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')
>> from tssds.ehefmsam
>> where locdesc = 'MW-8'
>> order by s_date
>> ;
>>
>> --
>>
>> Jonathan Lewis
>> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>>
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Aug 18 1999 - 07:50:47 CDT