question

Emilio avatar image
Emilio asked

SQL Functions error code 1172 Result Consisted of more than one row!

Hi i'm trying to create a SQL function but keep getting error code: 1172 Result Consisted of more than one row. Is there anyone who have an idea of what is wrong in the code. I want the function to calculate the avg agelimit and show the games which is over the avg. The results should consist of 18 values.

My code:

Delimiter //
CREATE FUNCTION fAgeLimit2() returns varchar(1000)
BEGIN
DECLARE GameName varchar(1000);
SELECT DISTINCT game.name into GameName from game, genre, hasgenre
WHERE game.name = hasgenre.GameName
AND hasgenre.GenreName = genre.Name
AND AgeLimit > (SELECT AVG(AgeLimit) FROM genre);
return GameName;
END //


mysqlfunctionsfunctioncreateavg
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered

You have defined a scalar-valued function, which will return a single value of varchar(1000). But your query is returning more than one row.

What did you intend for this function to do - if you only want a single value back, then you will need to change your query to do just that. If you want multiple rows back, then you should turn the this function into a table-valued function instead.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.