0

I just try following query:

CREATE DEFINER=`root`@`localhost` FUNCTION `cal1`(z DATE , y DATE) RETURNS float
BEGIN
SET @c= (SELECT a.StudentName, (b.marks/a.marks) as difference from (select Date, StudentName, marks from studenthistory2015 WHERE Date=z) as a INNER JOIN (select Date, StudentName, marks from studenthistory2015 where Date=y) as b on a.date=z and b.date=y WHERE a.date = z and b.date = y and a.StudentName=b.StudentName
);
RETURN @c;
END

Execution :

select student_history.cal1('2015-01-01', '2015-02-01'); But after execution, it shows Error #1241 Operand should contain 1 column(s)

How should I resolve this query?

P.Salmon
  • 17,104
  • 2
  • 12
  • 19
BTG123
  • 137
  • 1
  • 3
  • 13
  • The error is because you are trying to stuff a result set into a variable. I don't see the point of a function here since you seem to want to calculate for all students. – P.Salmon Nov 17 '17 at 09:58

1 Answers1

0

As P.Salmon said, you are trying to return a set of params, containing both strings and floats (i assume)

SELECT a.StudentName, (b.marks/a.marks) as difference

instead of returning a single variable, although you actually did declare, in your function, that you will return a float: RETURNS float

Maybe you can convert you function into a procedure, like mentioned in this detailed answer here, answering another question related to yours.

backbone
  • 98
  • 1
  • 1
  • 8