PL / SQL Anonymous Block "Fine Sample Returns More Than Requested Rows"
I was working on a PL / SQL script that calculates the similarity between two recipes. A recipe is a table that contains all the titles, cooking, etc. An ingredient is where all the unique ingredients are stored, and a recipe is the solution to the problem of many, many, and the two are related.
CREATE OR REPLACE FUNCTION func_similarity
(idS1 IN recipes.recipe.recipeID%type , idS2 IN recipes.recipe.recipeID%type )
RETURN NUMBER
AS
sim_value NUMBER := 0;
BEGIN
SELECT 2*
(select count(*) from recipes.ingredient i where i.ingredientID in (
Select distinct ingredientID from recipes.recipeing where recipeID = s1.recipeID
intersect
select distinct ingredientID from recipes.recipeing where recipeID = s2.recipeID))
/ ((select distinct count(ingredientID) from RECIPES.recipeing where recipeID = s1.recipeID) +
(select distinct count(ingredientID) from recipes.recipeing where recipeID = s2.recipeID) ) INTO sim_value
from recipes.recipe s1, recipes.recipe s2
where s1.recipeID = idS1
and s2.recipeID = idS2;
RETURN (sim_value);
END func_similarity;
/
However, when I go to check it with an anonymous block, I get an error: "fine sampling returns more than the requested number of rows"
declare
v_sim number;
begin
v_sim:=func_similarity(1,4);
dbms_output.put_line(v_sim);
end;
/
Now I'm sure the function makes sense and should work (I have all weekend). Anyone have any ideas as to why this might not work?
Thanks in advance.
source to share
There is no link between the two tables:
from recipes.recipe s1, recipes.recipe s2
where s1.recipeID = idS1
and s2.recipeID = idS2;
Therefore, the request is a cross join. It doesn't matter if it recipeID
is the primary key. But if you have duplicate numbers in that column, your query will return more than one row. If your query returns more than one row, your function will throw TOO_MANY_ROWS exception, because we can only select one INTO row of the variable (unless we use BULK COLLECT IN on the collection variable,
source to share