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

We now celebrate our 5 years of existence and break our frequentation records... Here are the stats of those 5 years.

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

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

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