Thursday, August 04, 2005

What I learnt about Oracle today


SQL> SELECT id, some_date
2 FROM tst1
3 ORDER BY some_date
4 /
ID SOME_DATE
---------- ---------
8 03-JAN-05
10 10-FEB-05
1 18-FEB-05
2 24-FEB-05
6 26-FEB-05
9 01-MAY-05
7 20-MAY-05
4 29-MAY-05
3 18-JUN-05
5 09-JUL-05

10 rows selected.

SQL> SELECT id, to_char(some_date, 'DD-MON-YYYY HH24:MI:SS') AS some_date
2 FROM tst1
3 ORDER BY some_date
4 /
ID SOME_DATE
---------- --------------------
9 01-MAY-2005 09:58:09
8 03-JAN-2005 11:00:33
5 09-JUL-2005 21:01:44
10 10-FEB-2005 03:19:58
1 18-FEB-2005 00:46:04
3 18-JUN-2005 03:06:05
7 20-MAY-2005 08:20:05
2 24-FEB-2005 08:17:43
6 26-FEB-2005 07:12:51
4 29-MAY-2005 14:21:38

10 rows selected.

SQL> SELECT id, to_char(some_date, 'DD-MON-YYYY HH24:MI:SS') AS the_date
2 FROM tst1
3 ORDER BY some_date
4 /
ID THE_DATE
---------- --------------------
8 03-JAN-2005 11:00:33
10 10-FEB-2005 03:19:58
1 18-FEB-2005 00:46:04
2 24-FEB-2005 08:17:43
6 26-FEB-2005 07:12:51
9 01-MAY-2005 09:58:09
7 20-MAY-2005 08:20:05
4 29-MAY-2005 14:21:38
3 18-JUN-2005 03:06:05
5 09-JUL-2005 21:01:44

10 rows selected.

SQL>


Hmmm....

Oracle's SELECT statements support column aliases in the ORDER BY clause. Now when did that happen? Actually Oracle's been doing so for years, since at least 8i. It's one of those things I feel a fool for not knowing about, but at the same time I'm glad there's still things about even very basic Oracle that I have yet to learn.

A piece of seventeenth Taoist wisdom, cited in The DailyZen Journal:


If people want to do the finest thing in the world, nothing compares to learning. If they want to be the best of learners, nothing compares to learning the Way.

7 comments:

Anonymous said...

Don't have access to an Oracle 7 db, but I've got the Server Manual PDF and it says
"c_alias ... provides a different name for the column expression and causes the alias to be used in the column heading.... The alias can be used in the ORDER BY clause, but not other clauses in the
query."

So it looks like it has been there a LONG time (and i didn't know about it either)

Anonymous said...

Andrew:

I have been lurking and enjoying your Blog for a while now since you postedt he link for Log4PlSQL.

You probably saw this, but this is what I learned about Oracle today :-)

http://forums.oracle.com/forums/thread.jspa?threadID=318471&tstart=0
John

Laurent Schneider said...

Dear APC,
You should edit the http://radiofreetooting.blogspot.com/2005/08/what-i-learnt-about-oracle-today.html post. One of its line is too long, and makes the whole blog looks ugly...

I mean the right frame is BELOW the middle frame.

I know that, I just fixed the same problem in my blog today!

Laurent Schneider said...

note that it looks ugly only in internet explorer, but fine in firefox... try to break your long lines of code and check if it is better afterwards.

If you do not want to support internet explorer, just blame me for my comment!

Cheers

Laurent Schneider said...

a good tip I found is to replace <PRE> tags by <CODE> tags to avoid those too large lines ;-)

Anonymous said...

情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣,情趣,情趣,情趣,情人歡愉用品,情趣用品,AIO交友愛情館,情人歡愉用品,美女視訊,情色交友,視訊交友,辣妹視訊,美女交友,嘟嘟成人網,按摩棒,震動按摩棒,微調按摩棒,情趣按摩棒,逼真按摩棒,G點,跳蛋,跳蛋,跳蛋,性感內衣,飛機杯,充氣娃娃,情趣娃娃,角色扮演,性感睡衣,SM,潤滑液,威而柔,香水,精油,芳香精油,自慰,自慰套,性感吊帶襪,情趣用品加盟,情人節禮物,情人節,吊帶襪,成人網站,AIO交友愛情館,情色,情色貼圖,情色文學,情色交友,色情聊天室,色情小說,七夕情人節,色情,A片,A片下載,免費A片,免費A片下載,情色電影,色情網站,辣妹視訊,視訊聊天室,情色視訊,免費視訊聊天,視訊聊天,美女視訊,視訊美女,美女交友,美女,情色交友,成人交友,自拍,本土自拍,情人視訊網,視訊交友90739,生日禮物,情色論壇,正妹牆,正妹,成人網站,A片,免費A片,A片下載,免費A片下載,AV女優,成人影片,色情A片,成人論壇,情趣,免費成人影片,成人電影,成人影城,愛情公寓,色情影片,保險套,舊情人,微風成人,成人,成人遊戲,成人光碟,色情遊戲,跳蛋,按摩棒,一夜情,男同志聊天室,肛交,口交,性交,援交,免費視訊交友,視訊交友,一葉情貼圖片區,性愛,視訊,嘟嘟成人網

愛情公寓,情色,舊情人,情色貼圖,情色文學,情色交友,色情聊天室,色情小說,一葉情貼圖片區,情色小說,色情,色情遊戲,情色視訊,情色電影,aio交友愛情館,色情a片,一夜情,辣妹視訊,視訊聊天室,免費視訊聊天,免費視訊,視訊,視訊美女,美女視訊,視訊交友,視訊聊天,免費視訊聊天室,情人視訊網,影音視訊聊天室,視訊交友90739,成人影片,成人交友,美女交友,微風成人,嘟嘟成人網,成人貼圖,成人電影,A片

TU said...

厨房機器 買取
トイレつまり
製氷機
トイレつまり
業務用 食器洗浄機
中古 冷蔵ショーケース
トイレつまり
過払い
頭が悪い
羽村市 不動産
SEO
エコキュート 価格
太陽光発電 神奈川
カップリングパーティー
賃貸 新築
楽器 レンタル
フランス留学
ニュージーランド留学
語学留学
高校留学
大学留学
大学院留学
不動産 埼玉
屋形船
酸素カプセル販売
屋形船貸切
ティーカッププードル販売