PDA

View Full Version : MYSQL "CREATE FUNCTION"



somebody
15-08-2007, 04:33 PM
Does anyone have example of a MYSQL "CREATE FUNCTION statement? Here is my (non-working) attempt: CREATE FUNCTION avgTopN (uID INT(10), n INT(10)) RETURNS INT(10) RETURN SELECT avg(score) FROM (SELECT score FROM score_table WHERE userID=uID ORDER BY score DESC LIMIT n) AS tbl1; I'm trying to find one which includes a SELECT as a return statement. The only examples I can find do not include the execution of SELECTs. Also, don't tell me to search on Google as that's what I've been doing for the past hour.

Bozo
15-08-2007, 08:31 PM
Sorry i can't actually help with the problem.
I am currently studying for B.I.T and on Friday we have our Database tutorial so will ask the instructor and see what we come up with.
(that is, if you can wait till Friday) :(

somebody
15-08-2007, 09:31 PM
That would be great if you could.

somebody
16-08-2007, 04:33 PM
[bump]

Bozo
16-08-2007, 05:55 PM
You're going to want to kill me.
Class is canceled tomorrow :annoyed:
So won't be able to get to ask tutor for help......yawn....
K, will take a look at it myself and see what happens.

What error messages etc. do you get when you try and run it?

somebody
16-08-2007, 07:29 PM
You're going to want to kill me.
Class is canceled tomorrow :annoyed:
So won't be able to get to ask tutor for help......yawn....
K, will take a look at it myself and see what happens.

What error messages etc. do you get when you try and run it?

No worries. The error I'm getting is "1064" Syntax error near "SELECT..."

TGoddard
18-08-2007, 02:52 PM
Try removing the RETURN before the SELECT. I'm no expert on MySQL but a glace at the help page would seem to indicate this is not legal syntax.

somebody
18-08-2007, 03:39 PM
Try removing the RETURN before the SELECT. I'm no expert on MySQL but a glace at the help page would seem to indicate this is not legal syntax.

I'd tried that, but that also didn't work. I was following an example here: http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html which uses the RETURN statement, without a SELECT (just concatenating two strings). In any case, I have found an alternative way of achieving what I wanted to achieve. Cheers for the help everyone.

Renmoo
18-08-2007, 04:50 PM
Hey man, I can try to ask the question for you on Auckland U's software engineering forum.

Which section would you like me to place the query under? https://forums.cs.auckland.ac.nz/

Cheers :)

somebody
18-08-2007, 04:56 PM
Hey man, I can try to ask the question for you on Auckland U's software engineering forum.

Which section would you like me to place the query under? https://forums.cs.auckland.ac.nz/

Cheers :)

Don't worry about it - got it sorted now using a different technique (a slightly more complex query).