visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
294 experts, 1172 registered users, 1625 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


The DDRK link is back online.
We got a one week service interruption, sorry.
(23-30/01/2010)

Databases :: Oracle :: oracle limitation ? Can't SELECT * with an added computed column ! ORA-00923
By: Nono Great Britain  Date: 11/05/2008 07:55:08  English French  Points: 20 Status: Answered
Quality : Excellent
bonjour,

je fais des SELECT * tout bêtes...

ajoutons une colonnes calculée dans un select * (marche en MySql...) :
I'm trying to add a computed column (a silly 10% discount on `total`) :

SQL> desc ord; Nom --------------------------------------------------------------------------------------------------- ORDID ORDERDATE COMMPLAN CUSTID SHIPDATE TOTAL SQL> select *,round(total*0.9,1) as "total après remise" from ord; select *,round(total*0.9,1) as "total après remise" from ord * ERREUR à la ligne 1 : ORA-00923: Mot-clé FROM absent à l'emplacement prévu



This works in MySql, which is supposed less compliant and less powerful than Oracle... How come ?
By: VGR Date: 11/05/2008 08:29:50 English  Type : Answer
Well, again Oracle demonstrates its lack of power by enforcing stupid limitations to the user.

Oracle requires that a SELECT * being written either as ONLY "select *", OR a full (complete) list of all column names - how handy :/ - when you want to add a computed column...

Proof : this will work...

SQL> select ordid,orderdate,commplan,custid,shipdate,total,round(total*0.9,1) as "total après remise" from ord;


Oracle is very very limited in power, as you can see.

On the other side, with no logical justification, Oracle is happy with your first syntax if you prefix the * with the table name... (ridiculous given this is a one table statement)

SQL> select a.*,round(total*0.9,1) as "total après remise" from ord a;



This will work too.

Don't try to understand why ;-)

Do register to be able to answer

 Add This Article To:
 del.icio.usDel.icio.us  diggDigg  googleGoogle  spurlSpurl
 blinkBlink  wongWong  simpySimpy  yahooY! MyWeb 
EContact
browser fav
page generated in 63.953160 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page