MySQL CREATE FUNCTION breaking on END IF

Question

I'm trying to create a function for calculating distances between two lat/lon points; however, the CREATE FUNCTION is breaking on the END IF; portion of the function body (confirmed by removing the IF/ELSE/END block).

Any ideas? I would like to keep the M / KM difference in there to save having to add calculations in the application.

DELIMITER $$

CREATE FUNCTION HAVERSINE
    (
        from_latitude DECIMAL(30, 15),
        from_longitude DECIMAL(30, 15),
        to_latitude DECIMAL(30, 15),
        to_longitude DECIMAL(30, 15),
        unit VARCHAR(20)
    )
    RETURNS DECIMAL(30, 15)
    DETERMINISTIC
    BEGIN
        DECLARE radius DECIMAL(30, 5);
        DECLARE distance DECIMAL(30, 15);

        IF(unit = 'MILES') THEN SET radius = '3959';
        ELSE IF(unit = 'KILOMETRES' OR unit = 'KM') THEN SET radius = '6371.3929';
        ELSE SET radius = '3959';
        END IF;

        SET distance = (
            radius * 
            ACOS(
                COS(RADIANS(from_latitude)) * 
                COS(RADIANS(to_latitude)) * 
                COS(RADIANS(to_longitude) - RADIANS(from_longitude)) + 
                SIN(RADIANS(from_latitude)) * 
                SIN(RADIANS(to_latitide))
            )
        );

        RETURN distance;
    END

score 0 · Answer 1 · answered May 12 '13 at 14:12

0

You probably forgot to add the delimiter at the end, and switch back to the standard delimiter:

Replace the last line with the following code:

END$$
DELIMITER ;

answered May 12 '13 at 14:12

Jocelyn

11,209
10
43
60

score 0 · Accepted Answer · answered May 12 '13 at 14:14

0

I just noticed the spaces between ELSE and IF. My bad. Removed those and it works.

answered May 12 '13 at 14:14

MySQL CREATE FUNCTION breaking on END IF

2 Answers2