Why does this mysql function keep returning zero value?

Question

so this is the code

CREATE FUNCTION smc() RETURNS FLOAT
 DETERMINISTIC
 BEGIN
DECLARE w1 FLOAT;
DECLARE w2 FLOAT;
DECLARE qd FLOAT;
DECLARE hasil FLOAT;
SET w1 = "SELECT TRUNCATE(SQRT(SUM((w1*w1))),2) FROM tb_term";
SET w2 = "SELECT TRUNCATE(SQRT(SUM((w2*w2))),2) FROM tb_term";
SET qd = "SELECT TRUNCATE(SUM(w1*w2),2) FROM tb_term";
SET hasil = (qd/(w1*w2)); 
RETURN hasil; END;

it keep return null value. when i return w1,w2 or qd it return 0 value. what's wrong ? thanks before.

Answer 1

You're declaring your w1, w2, and qd variables as FLOAT but you're assigning strings to them; the result is that you get 0.0 in all three. Then you assign 0.0/0.0 to hasil and dividing by zero gives you a NULL.

I think you're looking for

SELECT TRUNCATE(SQRT(SUM((w1*w1))),2) into w1 FROM tb_term;
SELECT TRUNCATE(SQRT(SUM((w2*w2))),2) into w2 FROM tb_term;
SELECT TRUNCATE(SUM(w1*w2),2) into qd FROM tb_term;

Answer 2

Try to rewrite your function in this way -

CREATE FUNCTION smc()
RETURNS FLOAT
DETERMINISTIC
BEGIN
 DECLARE w1 FLOAT;
 DECLARE w2 FLOAT;
 DECLARE qd FLOAT;
 DECLARE hasil FLOAT;
 SELECT truncate(sqrt(sum((w1 * w1))), 2) INTO w1 FROM tb_term;
 SELECT truncate(sqrt(sum((w2 * w2))), 2) INTO w2 FROM tb_term;
 SELECT truncate(sum(w1 * w2), 2) INTO qd FROM tb_term;
 SET hasil = (qd / (w1 * w2));
 RETURN hasil;
END

And rename declared variables w1, w2, they should not be the same as field names.

Answer 3

Set statement does not assign query result to the variable. In this case it just assign the query string. You can do this by SELECT INTO statement.

SELECT 
 TRUNCATE(SQRT(SUM((w1*w1))),2),
 TRUNCATE(SQRT(SUM((w2*w2))),2),
 TRUNCATE(SUM(w1*w2),2) 
INTO w1, w2, qd 
FROM tb_term

• Shiplu Mokaddim: @user1297348 Did you see the update? You can do it in one single query.