Announcement

Collapse
No announcement yet.

Issue with CREATE FUNCTION on MySQL 5

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Issue with CREATE FUNCTION on MySQL 5

    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
    Gareth Howells

    BSc (Hons), MBCS, MCP, MCDST, ICCE

    Any advice is given in good faith and without warranty.

    Please give reputation points if somebody has helped you.

    "For by now I could have stretched out my hand and struck you and your people with a plague that would have wiped you off the Earth." (Exodus 9:15) - I could kill you with my thumb.

    "Everything that lives and moves will be food for you." (Genesis 9:3) - For every animal you don't eat, I'm going to eat three.

  • #2
    Re: Issue with CREATE FUNCTION on MySQL 5

    Silly answer, possibly, but have you tried RETURN SELECT SUM(etc...)

    Alternatively SELECT into a variable and return the variable:
    http://www.databasejournal.com/featu...-Functions.htm
    Last edited by Ossian; 2nd August 2011, 11:22.
    Tom Jones
    MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
    PhD, MSc, FIAP, MIITT
    IT Trainer / Consultant
    Ossian Ltd
    Scotland

    ** Remember to give credit where credit is due and leave reputation points where appropriate **

    Comment


    • #3
      Re: Issue with CREATE FUNCTION on MySQL 5

      Yes but in the example in the documetation, SELECT is replaced by RETURN.
      Gareth Howells

      BSc (Hons), MBCS, MCP, MCDST, ICCE

      Any advice is given in good faith and without warranty.

      Please give reputation points if somebody has helped you.

      "For by now I could have stretched out my hand and struck you and your people with a plague that would have wiped you off the Earth." (Exodus 9:15) - I could kill you with my thumb.

      "Everything that lives and moves will be food for you." (Genesis 9:3) - For every animal you don't eat, I'm going to eat three.

      Comment


      • #4
        Re: Issue with CREATE FUNCTION on MySQL 5

        See edited comment above!
        Tom Jones
        MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
        PhD, MSc, FIAP, MIITT
        IT Trainer / Consultant
        Ossian Ltd
        Scotland

        ** Remember to give credit where credit is due and leave reputation points where appropriate **

        Comment


        • #5
          Re: Issue with CREATE FUNCTION on MySQL 5

          I'm giving up on this for now and just doing the required maths in the application. I'll likely revisit this in the future when I have more time to look into it, but for now I just want to get this app working. Thanks again for the suggestions, I'll post back with any findings
          Gareth Howells

          BSc (Hons), MBCS, MCP, MCDST, ICCE

          Any advice is given in good faith and without warranty.

          Please give reputation points if somebody has helped you.

          "For by now I could have stretched out my hand and struck you and your people with a plague that would have wiped you off the Earth." (Exodus 9:15) - I could kill you with my thumb.

          "Everything that lives and moves will be food for you." (Genesis 9:3) - For every animal you don't eat, I'm going to eat three.

          Comment

          Working...
          X