Example 7 11   MySQL implicit casting using concatenation for strings and DATE

mysql> select concat( ITSOSJ ,1234) from t1;

+           +

| stringcol |

+           +

| ITSOSJ1234|

+           +

1 row in set (0.02 sec)

mysql> select concat( ITSOSJ ,current_date) as stringdate from t1;

+                  +

| stringdate       |

+                  +

| ITSOSJ2004 01 23 |

+                  +

1 row in set (0.01 sec)

DB2 requires compatible arguments for the concatenation built in functions as 

shown in Example 7 12. If the arguments are not compatible (for example, one 

argument is of character data type and the second argument is of numeric data 

type) the concatenation will fail with the error message SQL0440.

Example 7 12   DB2 UDB casting character strings and DATE explicitly

db2 => select concat( ITSOSJ ,1234) from t1

SQL0440N  No authorized routine named "CONCAT" of type "FUNCTION" having

compatible arguments was found.  SQLSTATE=42884

db2 => select concat( ITSOSJ , 1234 ) as stringcol from t1

STRINGCOL

ITSOSJ1234

  1 record(s) selected.

db2 => select concat( ITSOSJ , current date) as stringdate from t1

SQL0440N  No authorized routine named "CONCAT" of type "FUNCTION" having

compatible arguments was found.  SQLSTATE=42884

db2 => select concat( ITSOSJ ,CAST(current date as char(20))) as stringdate 

from t1

STRINGDATE

ITSOSJ01/23/2004

 Chapter 7. Application porting 

165