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

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: Oracle :: oracle error ORA-00909 with CONCAT() having three arguments !
By: Nono Great Britain  Date: 11/05/2008 08:19:03  English  Points: 20 Status: Answered
Quality : Excellent
I'm trying a standard CONCAT() call in Oracle with three miserable arguments... and Oracle fails with ORA-00909 ?!??

SQL> desc customer Nom --------------------------------------------------------------------------------------------------- CUSTID NAME ADDRESS CITY STATE ZIP AREA PHONE REPID CREDITLIMIT COMMENTS SQL> select concat(name,' est suivi par le représentant numéro ',repid) AS 'client et représentant' from customer; select concat(name,' est suivi par le représentant numéro ',repid) AS 'client et représentant' from * ERREUR à la ligne 1 : ORA-00909: Nombre d'arguments incorrect



Having read the SQL standard, it seems to me that CONCAT() should work...
By: VGR Date: 11/05/2008 08:41:25 English  Type : Answer
Well, again you fell in an Oracle trap ;-)

Oracle has functions that have an open list of arguments (DECODE() for instance) BUT (sadly and illogically when comparing the useage rate of both functions) DOESN'T for CONCAT() !

CONCAT() in Oracle admits ONLY TWO ARGUMENTS !!!

so you'd to write concat(one,concat(two,concat(three,four))) in stead of concat(one,two,three,four) !
(it begins to read like LISP : lots of insipid and stupid parentheses... 8-))

Oracle tries to rely on the operator || rather than to provide a normally powerful concat(), so write this and it'll work :

SQL> select name || ' est suivi par le représentant numéro ' || repid AS "client et représentant" from customer;



Don't ask me why you've to write double quotes for the column alias, it's one more time an Oracle trap ;-)

The good story about using || is that it's supposed to be portable... except for the RDBMS that had to support C-style operators and provide a CONCAT() function in stead...

This time, it's the SQL-99 standard which made a gross mistake...

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 292.956110 milliseconds

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