IF statement in mysql returns failed result

[maeC]

Hi, I've been trying to find what is causing my problem here but I couldn't find any. I had a similar code and it works just fine.. This code always gives me a failed result.. The problem starts in IF

IF v_start_date > v_dateNow THEN
        INSERT INTO playlist_summary(playlist_name,playlist_id,playlist_status,plate_number,exposure) SELECT playlists.playlist_name,playlists.playlist_id,playlists.playlist_status,taxi.plate_number FROM playlists
        JOIN playlist_terminal
        ON playlist_terminal.userid = i_userid
        JOIN taxi
        ON taxi.terminal_id = playlist_terminal.terminal_id
        WHERE playlists.userid = i_userid AND playlists.playlist_id = i_playlistid;
 
       ELSE IF v_end_date = '0000-00-00 00:00:00' THEN
 
       INSERT INTO playlist_summary(playlist_name,playlist_id,playlist_status,plate_number,exposure) SELECT playlists.playlist_name,playlists.playlist_id,playlists.playlist_status,taxi.plate_number,
        ((((SUM(total_uptime.total_on_hours)) + total_uptime_perday.total_on_hours)*(60*60))/10)/(taxi.slots_taken + 3) AS exposure FROM playlists
        JOIN playlist_terminal
        ON playlist_terminal.userid = i_userid
        JOIN taxi
        ON taxi.terminal_id = playlist_terminal.terminal_id
        JOIN total_uptime
        ON total_uptime.terminal_id = playlist_terminal.terminal_id
        JOIN total_uptime_perday
        ON total_uptime_perday.terminal_id = playlist_terminal.terminal_id
        WHERE playlists.userid = i_userid AND playlists.playlist_id = i_playlistid AND total_uptime.start_time BETWEEN playlists.start_date AND (subdate(end_date,-1))
        GROUP BY playlist_terminal.ref_number;
 
        ELSE
 
        INSERT INTO playlist_summary(playlist_name,playlist_id,playlist_status,plate_number,exposure) SELECT playlists.playlist_name,playlists.playlist_id,playlists.playlist_status,taxi.plate_number,
        (((SUM(total_uptime.total_on_hours))*(60*60))/10)/(taxi.slots_taken + 3) AS exposure FROM playlists
        JOIN playlist_terminal
        ON playlist_terminal.userid = i_userid
        JOIN taxi
        ON taxi.terminal_id = playlist_terminal.terminal_id
        JOIN total_uptime
        ON total_uptime.terminal_id = playlist_terminal.terminal_id
        JOIN total_uptime_perday
        ON total_uptime_perday.terminal_id = playlist_terminal.terminal_id
        WHERE playlists.userid = i_userid AND playlists.playlist_id = i_playlsitid AND total_uptime.start_time BETWEEN playlists.start_date AND (subdate(end_date,-1))
        GROUP BY playlist_terminal.ref_number;
 
        END IF;
        END IF;

[Luuk]

"The problem starts in IF", and what is the exact error message?

[Luuk]

IF it's this one:

ERROR 1064 (42000): 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 'IF ........

Then the SQL syntax is wrong.

[maeC]

This is the error I am getting -- You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF.... at line 1

[Luuk]

a SQL statement can not start with 'IF'.

i do not see any reference to 'v_start_date', 'v_dateNow' or 'v_end_date' in your SQL-statements.

Can you explain what you want to achieve?

(It would be of help if the table structure is given for the tables used, and possible some sample data)

[maeC]

I'm sorry not being clear. Here's my complete code

CREATE DEFINER=`root`@`localhost` PROCEDURE `playlist_summary_exposure`(
                 IN i_userid INT(11),
                 IN i_playlistid INT(11)
 
    )
BEGIN
 
        DECLARE v_start_date DATE;
        DECLARE v_dateNow DATE;
        DECLARE v_end_date DATE;
 
    DECLARE o_success BOOLEAN;
    DECLARE o_message VARCHAR(255);
 
        SELECT start_date FROM playlists WHERE userid = i_userid INTO v_start_date;
        SELECT DATE(NOW()) INTO v_dateNow;
        SELECT end_date FROM playlists WHERE userid = i_userid INTO v_end_date;
 
      DELETE FROM playlist_summary;
 
      IF v_start_date > v_dateNow THEN
        INSERT INTO playlist_summary(playlist_name,playlist_id,playlist_status,plate_number,exposure) SELECT playlists.playlist_name,playlists.playlist_id,playlists.playlist_status,taxi.plate_number FROM playlists
        JOIN playlist_terminal
        ON playlist_terminal.userid = i_userid
        JOIN taxi
        ON taxi.terminal_id = playlist_terminal.terminal_id
        WHERE playlists.userid = i_userid AND playlists.playlist_id = i_playlistid;
 
       ELSE IF v_end_date = '0000-00-00 00:00:00' THEN
 
       INSERT INTO playlist_summary(playlist_name,playlist_id,playlist_status,plate_number,exposure) SELECT playlists.playlist_name,playlists.playlist_id,playlists.playlist_status,taxi.plate_number,
        ((((SUM(total_uptime.total_on_hours)) + total_uptime_perday.total_on_hours)*(60*60))/10)/(taxi.slots_taken + 3) AS exposure FROM playlists
        JOIN playlist_terminal
        ON playlist_terminal.userid = i_userid
        JOIN taxi
        ON taxi.terminal_id = playlist_terminal.terminal_id
        JOIN total_uptime
        ON total_uptime.terminal_id = playlist_terminal.terminal_id
        JOIN total_uptime_perday
        ON total_uptime_perday.terminal_id = playlist_terminal.terminal_id
        WHERE playlists.userid = i_userid AND playlists.playlist_id = i_playlistid AND total_uptime.start_time BETWEEN playlists.start_date AND (subdate(end_date,-1))
        GROUP BY playlist_terminal.ref_number;
 
        ELSE
 
        INSERT INTO playlist_summary(playlist_name,playlist_id,playlist_status,plate_number,exposure) SELECT playlists.playlist_name,playlists.playlist_id,playlists.playlist_status,taxi.plate_number,
        (((SUM(total_uptime.total_on_hours))*(60*60))/10)/(taxi.slots_taken + 3) AS exposure FROM playlists
        JOIN playlist_terminal
        ON playlist_terminal.userid = i_userid
        JOIN taxi
        ON taxi.terminal_id = playlist_terminal.terminal_id
        JOIN total_uptime
        ON total_uptime.terminal_id = playlist_terminal.terminal_id
        JOIN total_uptime_perday
        ON total_uptime_perday.terminal_id = playlist_terminal.terminal_id
        WHERE playlists.userid = i_userid AND playlists.playlist_id = i_playlsitid AND total_uptime.start_time BETWEEN playlists.start_date AND (subdate(end_date,-1))
        GROUP BY playlist_terminal.ref_number;
 
        END IF;
        END IF;
 
 
    COMMIT;
 
 
    SELECT o_success AS success,o_message AS message;
END

It's a stored procedure.

[Luuk]

I do not see anything wrong. If you get errors it might have something to do with the data.

some tips
Line 15 and 17 can be combined to:

SELECT start_date, end_date FROM playlists WHERE userid = i_userid INTO v_start_date, v_end_date;
 

Be sure there is a space on line 29 after 'ELSE', because if there i no space, the second 'END IF' (line 60) is wrong
(see: http://dev.mysql.com/doc/refman/5.6/en/if.html)