Issue with CREATE FUNCTION on MySQL 5

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #155718

    Morning all,

    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),
    UNIQUE (holiday_date)
    ) 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 using

    Code:
    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)
    RETURNS DECIMAL(2,1)
    READS SQL DATA
    RETURN SUM(h.holiday_hours) FROM holidays_db.holiday h

    Which gives

    Code:
    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)

    Thanks

Viewing 1 post (of 1 total)

You must be logged in to reply to this topic.