"Ted" <r.ted.byersrogers.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;This is easy. The MySQL command line client *interprets* lines you> 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
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:
[url]http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html[/url]
XL
--
Axel Schwenke, Senior Software Developer, MySQL AB
Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
MySQL User Forums: [url]http://forums.mysql.com/[/url]
Bookmarks