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.

mu is too short · Accepted Answer · 2012-03-28 06:50:32Z

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;

answered Mar 28, 2012 at 6:50

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Devart · Accepted Answer · 2012-03-28 06:52:20Z

1

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.

answered Mar 28, 2012 at 6:52

Devart

120k23 gold badges172 silver badges189 bronze badges

Comments

Shiplu Mokaddim · Accepted Answer · 2012-03-28 07:07:59Z

1

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

edited Mar 28, 2012 at 7:07

answered Mar 28, 2012 at 6:54

Shiplu Mokaddim

57.5k20 gold badges147 silver badges193 bronze badges

1 Comment

Shiplu Mokaddim Over a year ago

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

Collectives™ on Stack Overflow

Why does this mysql function keep returning zero value?

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related