CODEWITHSUNDEEP
mysqlsqlfunction
Lopfest
Lopfest

Reputation: 113

MySQL function does not return correct result

The function below always returns ELSE value (which is 6) even if the SELECT query itself returns value 30.00 for example. In that case, the function should return number 4. Can anyone help me please?

DELIMITER $$
CREATE FUNCTION znamka(id_testu INT, id_studenta INT)
    RETURNS TINYINT
BEGIN
    DECLARE percenta FLOAT;
    SET percenta=
    (
        SELECT ROUND(tv.pocet_bodov/t.max_body*100, 2)
        FROM test t JOIN test_vysledky tv ON (tv.fk_test=t.id_test)
        WHERE tv.fk_test=id_testu AND tv.fk_student=id_studenta
    );
    RETURN CASE
        WHEN percenta>=90 THEN 1
        WHEN percenta<90 >=75 THEN 2
        WHEN percenta<75 >=50 THEN 3
        WHEN percenta<50 >=30 THEN 4
        WHEN percenta<30 THEN 5
        ELSE 6
    END;
END;
$$
DELIMITER ;

Upvotes: 1

Views: 117

Answers (2)

dnoeth
dnoeth

Reputation: 60462

What's the datatype of those pocet_bodov and max_body columns? If it's INT you might get a truncated result. Try multiplication first:

SELECT ROUND(100*tv.pocet_bodov/t.max_body, 2)

Edit: Previous can't cause a problem in MySQL because it's not doing INTEGER calculation.

The SQL CASE statement is not the same as a C SWITCH; it stops at the first matching condition, there is no need to use BETWEEN:

RETURN CASE
    WHEN percenta >= 90 THEN 1
    WHEN percenta >=75) THEN 2
    WHEN percenta >=50) THEN 3
    WHEN percenta >=30) THEN 4
    WHEN percenta < 30 THEN 5
    ELSE 6 -- this will only be returned for NULLs
END;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

Change your CASE statement to this:

RETURN CASE
    WHEN percenta >= 90 THEN 1
    WHEN (percenta < 90 AND percenta >=75) THEN 2
    WHEN (percenta < 75 AND percenta >=50) THEN 3
    WHEN (percenta < 50 AND percenta >=30) THEN 4
    WHEN percenta < 30 THEN 5
    ELSE 6
END;

Comments:

As @Drew mentioned in his comment, you will never hit your ELSE condition. In addition, I'm surprised that MySQL was executing this function at all without any error.

Upvotes: 2

Related Questions