gforceindustriesMemberAug 02, 2011 at 4:08 am #155718
I have a very simple database which contains two tables, no links between them. It’d be perfectly sufficient to represent this data in a spreadsheet, but I want it to be accessible via a web app, hence the database.
The table in question stores details of the holiday’s I’ve booked from work.Code:CREATE TABLE holidays_db.holiday (
holiday_id TINYINT NOT NULL AUTO_INCREMENT,
holiday_hours DECIMAL(2,1) NOT NULL,
holiday_date DATE NOT NULL,
holiday_reason VARCHAR (30) NOT NULL,
holiday_bort ENUM(“b”, “t”) DEFAULT “b” NOT NULL,
PRIMARY KEY (holiday_id),
) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_bin;
I’m trying to create a stored function to return the number of allocated hours. I can get this from the database usingCode:SELECT SUM(h.holiday_hours) FROM holiday h
What I can’t seem to do is create a function to do this for me. I want to use a function rather than a procedure because I don’t want to have to execute multiple statements (ie a CALL followed by a SELECT), as I will want to use this value in other SELECT operations.
I’ve tried various permutations of the CREATE FUNCTION syntax based on the MySQL 5 documentation, all with the same error. The code to create my function is:Code:CREATE FUNCTION holidays_db.allocated_hours (i INT)
READS SQL DATA
RETURN SUM(h.holiday_hours) FROM holidays_db.holiday h
Which givesCode:ERROR 1064 (42000): 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 ‘FROM holidays_db.holiday h’ at line 4
Can anybody give me a hint please? :)
To confirm, the select statement doesn’t appear to be the problem:Code:mysql> SELECT SUM(h.holiday_hours) FROM holidays_db.holiday h;
| SUM(h.holiday_hours) |
| 62.5 |
1 row in set (0.00 sec)
You must be logged in to reply to this topic.