By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,501 Members | 2,871 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,501 IT Pros & Developers. It's quick & easy.

IF statement in mysql returns failed result

P: 5
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

Expand|Select|Wrap|Line Numbers
  1. IF v_start_date > v_dateNow THEN
  2.         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
  3.         JOIN playlist_terminal
  4.         ON playlist_terminal.userid = i_userid
  5.         JOIN taxi
  6.         ON taxi.terminal_id = playlist_terminal.terminal_id
  7.         WHERE playlists.userid = i_userid AND playlists.playlist_id = i_playlistid;
  8.  
  9.        ELSE IF v_end_date = '0000-00-00 00:00:00' THEN
  10.  
  11.        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,
  12.         ((((SUM(total_uptime.total_on_hours)) + total_uptime_perday.total_on_hours)*(60*60))/10)/(taxi.slots_taken + 3) AS exposure FROM playlists
  13.         JOIN playlist_terminal
  14.         ON playlist_terminal.userid = i_userid
  15.         JOIN taxi
  16.         ON taxi.terminal_id = playlist_terminal.terminal_id
  17.         JOIN total_uptime
  18.         ON total_uptime.terminal_id = playlist_terminal.terminal_id
  19.         JOIN total_uptime_perday
  20.         ON total_uptime_perday.terminal_id = playlist_terminal.terminal_id
  21.         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))
  22.         GROUP BY playlist_terminal.ref_number;
  23.  
  24.         ELSE
  25.  
  26.         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,
  27.         (((SUM(total_uptime.total_on_hours))*(60*60))/10)/(taxi.slots_taken + 3) AS exposure FROM playlists
  28.         JOIN playlist_terminal
  29.         ON playlist_terminal.userid = i_userid
  30.         JOIN taxi
  31.         ON taxi.terminal_id = playlist_terminal.terminal_id
  32.         JOIN total_uptime
  33.         ON total_uptime.terminal_id = playlist_terminal.terminal_id
  34.         JOIN total_uptime_perday
  35.         ON total_uptime_perday.terminal_id = playlist_terminal.terminal_id
  36.         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))
  37.         GROUP BY playlist_terminal.ref_number;
  38.  
  39.         END IF;
  40.         END IF;
Aug 29 '14 #1
Share this Question
Share on Google+
6 Replies


Expert 100+
P: 1,035
"The problem starts in IF", and what is the exact error message?
Aug 29 '14 #2

Expert 100+
P: 1,035
IF it's this one:
Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 29 '14 #3

P: 5
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
Aug 30 '14 #4

Expert 100+
P: 1,035
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)
Aug 30 '14 #5

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

Expand|Select|Wrap|Line Numbers
  1. CREATE DEFINER=`root`@`localhost` PROCEDURE `playlist_summary_exposure`(
  2.                  IN i_userid INT(11),
  3.                  IN i_playlistid INT(11)
  4.  
  5.     )
  6. BEGIN
  7.  
  8.         DECLARE v_start_date DATE;
  9.         DECLARE v_dateNow DATE;
  10.         DECLARE v_end_date DATE;
  11.  
  12.     DECLARE o_success BOOLEAN;
  13.     DECLARE o_message VARCHAR(255);
  14.  
  15.         SELECT start_date FROM playlists WHERE userid = i_userid INTO v_start_date;
  16.         SELECT DATE(NOW()) INTO v_dateNow;
  17.         SELECT end_date FROM playlists WHERE userid = i_userid INTO v_end_date;
  18.  
  19.       DELETE FROM playlist_summary;
  20.  
  21.       IF v_start_date > v_dateNow THEN
  22.         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
  23.         JOIN playlist_terminal
  24.         ON playlist_terminal.userid = i_userid
  25.         JOIN taxi
  26.         ON taxi.terminal_id = playlist_terminal.terminal_id
  27.         WHERE playlists.userid = i_userid AND playlists.playlist_id = i_playlistid;
  28.  
  29.        ELSE IF v_end_date = '0000-00-00 00:00:00' THEN
  30.  
  31.        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,
  32.         ((((SUM(total_uptime.total_on_hours)) + total_uptime_perday.total_on_hours)*(60*60))/10)/(taxi.slots_taken + 3) AS exposure FROM playlists
  33.         JOIN playlist_terminal
  34.         ON playlist_terminal.userid = i_userid
  35.         JOIN taxi
  36.         ON taxi.terminal_id = playlist_terminal.terminal_id
  37.         JOIN total_uptime
  38.         ON total_uptime.terminal_id = playlist_terminal.terminal_id
  39.         JOIN total_uptime_perday
  40.         ON total_uptime_perday.terminal_id = playlist_terminal.terminal_id
  41.         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))
  42.         GROUP BY playlist_terminal.ref_number;
  43.  
  44.         ELSE
  45.  
  46.         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,
  47.         (((SUM(total_uptime.total_on_hours))*(60*60))/10)/(taxi.slots_taken + 3) AS exposure FROM playlists
  48.         JOIN playlist_terminal
  49.         ON playlist_terminal.userid = i_userid
  50.         JOIN taxi
  51.         ON taxi.terminal_id = playlist_terminal.terminal_id
  52.         JOIN total_uptime
  53.         ON total_uptime.terminal_id = playlist_terminal.terminal_id
  54.         JOIN total_uptime_perday
  55.         ON total_uptime_perday.terminal_id = playlist_terminal.terminal_id
  56.         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))
  57.         GROUP BY playlist_terminal.ref_number;
  58.  
  59.         END IF;
  60.         END IF;
  61.  
  62.  
  63.     COMMIT;
  64.  
  65.  
  66.     SELECT o_success AS success,o_message AS message;
  67. END
It's a stored procedure.
Sep 1 '14 #6

Expert 100+
P: 1,035
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT start_date, end_date FROM playlists WHERE userid = i_userid INTO v_start_date, v_end_date;
  2.  
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)
Sep 1 '14 #7

Post your reply

Sign in to post your reply or Sign up for a free account.