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

Question

question

Emilio asked Apr 27, '20

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 //

mysql functions function create avg

10 |1200 characters needed characters left characters exceeded

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

Answer 1 · 2020-04-27T07:52:03Z

Kev Riley answered Apr 27, '20

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

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

question