I am getting an error near the end of my function and the only hint is that it is near ''-CodePudding

I've tried reading several other similar answers to this, but none of them as far as I can tell are fixed the same way. I've tried deleting all new lines/tabs to try to remove invisible characters and even just putting it on one line.

I'm not the best at SQL once you get past a certain point, so this problem just me not realizing something, but any help would be appreciated.

The exact error is #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 66

and line 66 is RETURN points; right above the END;;

DELIMITER ;;
CREATE FUNCTION getPointValue(run_id INT)
RETURNS INT
BEGIN
    DECLARE points INT DEFAULT 0;
    DECLARE total_completions INT DEFAULT 1;
    DECLARE place INT;
    DECLARE time FLOAT(12,4);
    DECLARE style INT;
    DECLARE run_type INT;

    DECLARE percentile FLOAT(12,4);
    DECLARE total_points INT DEFAULT 3000;
    DECLARE percentile_potential INT;

    DECLARE temp FLOAT;

    DECLARE bracket_min INT DEFAULT 0;
    DECLARE bracket_max INT DEFAULT 0;

    SELECT time INTO time FROM surf_run WHERE run_id = run_id;
    SELECT style INTO style FROM surf_run WHERE run_id = run_id;
    SELECT run_type INTO run_type FROM surf_run WHERE run_id = run_id;

    SELECT COUNT(*) INTO place FROM surf_run WHERE time <= time AND style = style AND run_type = run_type AND best_run = TRUE;
    SELECT COUNT(*) INTO total_completions FROM surf_run WHERE style = style AND run_type = run_type AND best = TRUE;

    IF place = 1 THEN
        SET points = 10000;
    ELSE IF place = 2 THEN
        SET points = 7500;
    ELSE IF place = 3 THEN
        SET points = 5000;
    ELSE IF place = 4 THEN
        SET points = 3500;
    ELSE IF place = 5 THEN
        SET points = 2500;
    ELSE

        SET percentile = (place / total_completions);

        IF percentile <= 0.05 THEN
            SET percentile_potential = 3000;
            SET points = 2000;
            SET bracket_min = 0;
            SET bracket_max = total_completions * 0.05;
        ELSE IF percentile <= 0.10 THEN
            SET bracket_max = total_completions * 0.05   1;
            SET bracket_max = total_completions * 0.1;
            SET percentile_potential = 2000;
            SET points = 1250;
        ELSE IF percentile <= 0.15 THEN
            SET bracket_max = total_completions * 0.1   1;
            SET bracket_max = total_completions * 0.15;
            SET percentile_potential = 1250;
            SET points = 750;
        ELSE IF percentile <= 0.25 THEN
            SET bracket_max = total_completions * 0.15   1;
            SET bracket_max = total_completions * 0.25;
            SET percentile_potential = 750;
            SET points = 10;
        END IF;

        SET points = points   GREATEST(0, ROUND(percentile_potential - percentile_potential * LOG(place, bracket_max)));
    END IF;
    RETURN points;
END;;
DELIMITER ;

CodePudding user response：

Common "beauty" syntax error.

You use ELSE IF - in this case IF is not an alternative branch but separate IF statement. Hence you obtain multiple IF statements without END IF which produces an error.

Investigate MySQL 8.0 Reference Manual / ... / IF Statement - ELSEIF does not contain/allow space char.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a4450a40ab690e82ffa18bb3978e68c0