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.

Labels: ,

11 Comments:

Anonymous 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)

4 August 2005 at 15:54:00 GMT-7  
Anonymous John Spencer 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

5 August 2005 at 13:37:00 GMT-7  
Blogger 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!

14 September 2005 at 07:44:00 GMT-7  
Blogger 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

14 September 2005 at 15:19:00 GMT-7  
Blogger Laurent Schneider said...

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

17 January 2006 at 05:18:00 GMT-8  
Anonymous lilpitor said...

companies marketing mineral makeups and also get the best bargains in mineral makeup you can imagine,
find aout how to consolidate your students loans or just how to lower your actual rates.,
looking for breast enlargements? in Rochester,
homeopathy for eczema learn about it.,
Allergies, information about lipitor,
save big with great bargains in mineral makeup,

change edition interviewing motivational people preparing second
,

interviewing motivational people preparing second time
,

interviewing people motivational preparing for a second time
,

black mold exposure
,

black mold exposure symptoms
,

black mold symptoms of exposure
,

free job interview questions
,

free job interview answers
,

interview answers to get a job
,

lookfor hair styles for fine thin hair
,

search hair styles for fine thin hair
,

hair styles for fine thin hair
,

beach resort in the philippines
,

great beach resort in the philippines
,

luxury beach resort in the philippines
,
iron garden gates, here,
iron garden gates,
wrought iron garden gates
, here
,
wrought iron garden gates
,
You: The Owner's Manual: An Insider's Guide to the Body That Will Make You Healthier and Younger
,
eat eating mindless more than think we we why
,


texturizer,
texturizers here,
black hair texturizer,
find aout how care curly hair,
find about how to care curly hair,
care curly hair,
lipitor rash,
lipitor reactions,
new house ventura california,
the house new houston tx,
new house washington dc,
new house pa philadelphia,
san antonio tx house new,
house new pa philadelphia,
new house washington dc,
new house ventura california,
the house new houston tx,
house new san antonio tx,
the house new houston tx, that you are looking for,
new house ventura california, you need to buy,
new house washington dc,
house new pa philadelphia,
new house san antonio tx,

hair surgery transplant
,

air filter allergy
,

refurbished dell laptop computers
,

hair surgery transplant
,

air filter allergy
,

refurbished dell laptop computers
,

hair surgery transplant
,

air filter allergy
,

refurbished dell laptop computers
,

chocolate esophagus heartburn study
,

chocolate esophagus heartburn study
be informed,

digestion healing healthy heartburn natural preventing way
,

digestion healing healthy heartburn natural preventing way
,
sew skirts, 16simple styles you can make!,
sew what skirts 16 simple styles you,
rebates and discounts on sunsetter awnings,
sunsetter awnings discounts and rebates,
discount on sunsetter awnings


truck and bus tires 12r 22.5, get the best price,
tires truck and bus 12r 22.5 best price,
tires truck bus tires12r 22.5 best price,
plush car seat strap covers,
car seat strap covers,plush,
car seat strap, plush covers,
oscoda voip phone systems, the best!,
oscoda voip the phone system,
oscoda voip phone systems,
exterior iron gates,
oriental wrought iron gates,
powder coated iron garden fencing,

9 February 2008 at 10:08:00 GMT-8  
Blogger Manikandan said...

Hi .nice blog.I am HR of a well-developing concern.I need to post jobs .can anybody suggest best way..
thank you............

28 February 2008 at 20:01:00 GMT-8  
Anonymous Anonymous said...

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

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

11 October 2008 at 09:22:00 GMT-7  
Blogger cvxv said...

看房子,買房子,建商自售,自售,台北新成屋,台北豪宅,新成屋,豪宅,美髮儀器,美髮,儀器,髮型,EMBA,MBA,學位,EMBA,專業認證,認證課程,博士學位,DBA,PHD,在職進修,碩士學位,推廣教育,DBA,進修課程,碩士學位,網路廣告,關鍵字廣告,關鍵字,課程介紹,學分班,文憑,牛樟芝,段木,牛樟菇,日式料理, 台北居酒屋,日本料理,結婚,婚宴場地,推車飲茶,港式點心,尾牙春酒,台北住宿,國內訂房,台北HOTEL,台北婚宴,飯店優惠,台北結婚,場地,住宿,訂房,HOTEL,飯店,造型系列,學位,SEO,婚宴,捷運,學區,美髮,儀器,髮型,看房子,買房子,建商自售,自售,房子,捷運,學區,台北新成屋,台北豪宅,新成屋,豪宅,學位,碩士學位,進修,在職進修, 課程,教育,學位,證照,mba,文憑,學分班,台北住宿,國內訂房,台北HOTEL,台北婚宴,飯店優惠,住宿,訂房,HOTEL,飯店,婚宴,台北住宿,國內訂房,台北HOTEL,台北婚宴,飯店優惠,住宿,訂房,HOTEL,飯店,婚宴,台北住宿,國內訂房,台北HOTEL,台北婚宴,飯店優惠,住宿,訂房,HOTEL,飯店,婚宴,結婚,婚宴場地,推車飲茶,港式點心,尾牙春酒,台北結婚,場地,結婚,場地,推車飲茶,港式點心,尾牙春酒,台北結婚,婚宴場地,結婚,婚宴場地,推車飲茶,港式點心,尾牙春酒,台北結婚,場地,居酒屋,燒烤,美髮,儀器,髮型,美髮,儀器,髮型,美髮,儀器,髮型,美髮,儀器,髮型,小套房,小套房,進修,在職進修,留學,證照,MBA,EMBA,留學,MBA,EMBA,留學,進修,在職進修,牛樟芝,段木,牛樟菇,關鍵字排名,網路行銷,PMP,在職專班,研究所在職專班,碩士在職專班,PMP,證照,在職專班,研究所在職專班,碩士在職專班,SEO,廣告,關鍵字,關鍵字排名,網路行銷,網頁設計,網站設計,網站排名,搜尋引擎,網路廣告,SEO,廣告,關鍵字,關鍵字排名,網路行銷,網頁設計,網站設計,網站排名,搜尋引擎,網路廣告,SEO,廣告,關鍵字,關鍵字排名,網路行銷,網頁設計,網站設計,網站排名,搜尋引擎,網路廣告,SEO,廣告,關鍵字,關鍵字排名,網路行銷,網頁設計,網站設計,網站排名,搜尋引擎,網路廣告,EMBA,MBA,PMP,在職進修,專案管理,出國留學,EMBA,MBA,PMP,在職進修,專案管理,出國留學,EMBA,MBA,PMP,在職進修,專案管理,出國留學,婚宴,婚宴,婚宴,婚宴,漢高資訊,漢高資訊,比利時,比利時聯合商學院,宜蘭民宿,台東民宿,澎湖民宿,墾丁民宿,花蓮民宿,SEO,找工作,汽車旅館,阿里山,日月潭,阿里山民宿,東森購物,momo購物台,pc home購物,購物漢高資訊,漢高資訊,在職進修,漢高資訊,在職進修,民宿,民宿,整形,造型,室內設計,室內設計,漢高資訊,在職進修,漢高資訊,在職進修,民宿,美容,室內設計,在職進修,羅志祥,周杰倫,五月天,民宿,民宿,整形,整形,室內設計,室內設計,比利時聯合商學院,在職進修,比利時聯合商學院,在職進修,漢高資訊,找工作,找工作,找工作,找工作,找工作,蔡依林,林志玲

14 April 2009 at 00:48:00 GMT-7  
Anonymous TU said...

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

1 June 2009 at 07:10:00 GMT-7  
Blogger Jerry said...

cheap wedding gowns
discount bridal gowns
China wedding dresses
discount designer wedding dresses
China wedding online store
plus size wedding dresses
cheap informal wedding dresses
junior bridesmaid dresses
cheap bridesmaid dresses
maternity bridesmaid dresses
discount flower girl gowns
cheap prom dresses
party dresses
evening dresses
mother of the bride dresses
special occasion dresses
cheap quinceanera dresses
hot red wedding dresses

18 June 2009 at 08:27:00 GMT-7  

Post a Comment

<< Home