"Ted" <r.ted.byers@rogers.com> wrote:

> CREATE FUNCTION `alert_db`.`my_max_market_date` () RETURNS DATE
> BEGIN
> DECLARE mmmd DATE;
> SELECT max(h_market_date) into mmmd FROM holdings_tmp;
> RETURN mmmd;
> END;

> The problem is that when I try to put this into a script to be executed
> from a command line such as the following, it fails!
>
> mysql -u root -p --database=alert_db < Date_Calculation.sql
>
> The error I get is:
>
> ERROR 1064 (42000) at line 1: You have an error in your SQL syntax;
> check the manual that corresponds to your MySQL server version for the
> right syntax to use near 'DATE' at line 3

This is easy. The MySQL command line client *interprets* lines you
type. Especially it scans for the DELIMITER to break your lines into
SQL statements. The default delimiter is ';'. So your SQL statement
ends with the 'DATE' in line 3. It is thus incomplete and yields a
syntax error.

The solution is easy: specify a temporary new delimiter for mysql

mysql> DELIMITER //
mysql> CREATE FUNCTION my_max_market_date () RETURNS DATE
-> BEGIN
-> DECLARE mmmd DATE;
-> SELECT max(h_market_date) into mmmd FROM holdings_tmp;
-> RETURN mmmd;
-> END//
Query OK, 0 rows affected (1,95 sec)

mysql> DELIMITER ;

This trick is also shown in the manual:
http://dev.mysql.com/doc/refman/5.0/...procedure.html


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/