MySQL syntax error.. Please help...
-
Hi, I am getting the following error on MySQL stored procedure but I can't understand the reason! can any help please.. here is the error followed by full code:
[SQL]
IF EXISTS(SELECT visit\_id FROM visits WHERE file\_no = param\_file\_no) THEN BEGIN SET @param\_days\_since\_last\_visit = (DATEDIFF(DATE(NOW()), (SELECT DATE(created\_date) FROM visits where file\_no = param\_file\_noorder by created\_date DESC LIMIT 1)));[Err] 1064 - 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 EXISTS(SELECT visit_id FROM visits WHERE file_no = param_file_no) THEN
BEGIN
SET' at line 1full code:
BEGIN
DECLARE param_item_id_v1 int;
DECLARE param_item_id_v2 int;
DECLARE param_file_cutoff_days int;
DECLARE param_days_since_last_visit int;DECLARE param\_account\_number BIGINT; DECLARE param\_price\_list\_id INT; DECLARE param\_v1\_fees DOUBLE(11, 5); DECLARE param\_v2\_fees DOUBLE(11, 5); DECLARE param\_item\_description\_v1 VARCHAR(255); DECLARE param\_item\_description\_v2 VARCHAR(255); SET param\_account\_number = (SELECT account\_number FROM visits WHERE visit\_id = param\_visit\_id); SET param\_price\_list\_id = (SELECT price\_list\_id FROM visits WHERE visit\_id = param\_visit\_id); SET param\_item\_id\_v1 = (SELECT item\_id FROM price\_list\_items WHERE item\_code = param\_v1); SET param\_item\_description\_v1 = (SELECT item\_name FROM price\_list\_items WHERE item\_id = param\_item\_id\_v1); SET param\_v1\_fees = (SELECT price\_list\_item\_prices.item\_price FROM price\_list\_item\_prices INNER JOIN price\_list\_items ON price\_list\_item\_prices.item\_id = price\_list\_items.item\_id WHERE price\_list\_items.item\_code = param\_v1 AND price\_list\_item\_prices.price\_list\_id = param\_price\_list\_id); SET param\_item\_id\_v2 = (SELECT item\_id FROM price\_list\_items WHERE item\_code = param\_v2); SET param\_item\_description\_v2 = (SELECT item\_name FROM price\_list\_items WHERE item\_id = param\_item\_id\_v2); SET param\_v2\_fees = (SELECT price\_list\_item\_prices.item\_price FROM price\_list\_item\_prices INNER JOIN price\_list\_items ON price\_list\_item\_prices.item\_id = price\_list\_items.item\_id WHERE price\_list\_items.item\_code = param\_v2 AND price\_list\_item\_prices.price\_list\_id = param\_price\_list\_id); SET param\_file\_cutoff\_days = (SELECT system\_param\_value FROM system\_param WHERE system\_param\_name = 'FILE\_CUTOFF\_DAYS'); IF EXISTS(SELECT visit\_id FROM visits WHERE file\_no = param\_file\_no) THEN BEGIN SET param\_days\_I cannot immediately see an error so what I would suggest is running each assignment statement outside of the stored procedure noting down the results. Then use what you have noted down as the input for your exists clause. Keep working through the code in this manner until you find the error - yes, debugging is tedious however the gold at the end of the rainbow is finding the error. Good luck :)
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
I cannot immediately see an error so what I would suggest is running each assignment statement outside of the stored procedure noting down the results. Then use what you have noted down as the input for your exists clause. Keep working through the code in this manner until you find the error - yes, debugging is tedious however the gold at the end of the rainbow is finding the error. Good luck :)
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
that's what I did and I got the error mentioned in my first post.
[SQL]
IF EXISTS(SELECT visit\_id FROM visits WHERE file\_no = param\_file\_no) THEN BEGIN SET @param\_days\_since\_last\_visit = (DATEDIFF(DATE(NOW()), (SELECT DATE(created\_date) FROM visits where file\_no = param\_file\_noorder by created\_date DESC LIMIT 1)));[Err] 1064 - 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 EXISTS(SELECT visit_id FROM visits WHERE file_no = param_file_no) THEN
BEGIN
SET' at line 1Technology News @ www.JassimRahma.com
-
that's what I did and I got the error mentioned in my first post.
[SQL]
IF EXISTS(SELECT visit\_id FROM visits WHERE file\_no = param\_file\_no) THEN BEGIN SET @param\_days\_since\_last\_visit = (DATEDIFF(DATE(NOW()), (SELECT DATE(created\_date) FROM visits where file\_no = param\_file\_noorder by created\_date DESC LIMIT 1)));[Err] 1064 - 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 EXISTS(SELECT visit_id FROM visits WHERE file_no = param_file_no) THEN
BEGIN
SET' at line 1Technology News @ www.JassimRahma.com
Jassim Rahma wrote:
that's what I did
and yet what I am seeing in your code are variables such as
visit_idandparam_file_norather than the hard-coded values of these variables. I don't need to see the values but I am not sure you understand what I was trying to communicate in my previous post - basically what I am saying is run each part of the stored procedure one line at a time noting the results then run the next line - each time running the line outside of the stored procedure.“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Jassim Rahma wrote:
that's what I did
and yet what I am seeing in your code are variables such as
visit_idandparam_file_norather than the hard-coded values of these variables. I don't need to see the values but I am not sure you understand what I was trying to communicate in my previous post - basically what I am saying is run each part of the stored procedure one line at a time noting the results then run the next line - each time running the line outside of the stored procedure.“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
that's exactly what I did Christopher. All lines are giving correct results with no error. The line I am having problem with is the following:
SET @param_days_since_last_visit = (DATEDIFF(DATE(NOW()), (SELECT DATE(created_date) FROM visits where file_no = param_file_noorder by created_date DESC LIMIT 1)));
That's the same line mentioned earlier.
Technology News @ www.JassimRahma.com
-
that's exactly what I did Christopher. All lines are giving correct results with no error. The line I am having problem with is the following:
SET @param_days_since_last_visit = (DATEDIFF(DATE(NOW()), (SELECT DATE(created_date) FROM visits where file_no = param_file_noorder by created_date DESC LIMIT 1)));
That's the same line mentioned earlier.
Technology News @ www.JassimRahma.com
Jassim Rahma wrote:
_noorder by
That's your issue - you are missing a space before the
orderkeyword.“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Jassim Rahma wrote:
_noorder by
That's your issue - you are missing a space before the
orderkeyword.“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
no it's not. sorry i think it's paste issue. this is the line as it is in MySQL server;
SET param_days_since_last_visit = DATEDIFF(DATE(NOW()), (SELECT DATE(created_date) FROM visits where file_no = param_file_no order by created_date DESC LIMIT 1));
Technology News @ www.JassimRahma.com
-
no it's not. sorry i think it's paste issue. this is the line as it is in MySQL server;
SET param_days_since_last_visit = DATEDIFF(DATE(NOW()), (SELECT DATE(created_date) FROM visits where file_no = param_file_no order by created_date DESC LIMIT 1));
Technology News @ www.JassimRahma.com
Replace
Jassim Rahma wrote:
LIMIT 1)
with
LIMIT 0,1)
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Replace
Jassim Rahma wrote:
LIMIT 1)
with
LIMIT 0,1)
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
[SQL]
IF EXISTS(SELECT visit\_id FROM visits WHERE file\_no = 80016) THEN BEGIN SET @param\_days\_since\_last\_visit = (DATEDIFF(DATE(NOW()), (SELECT DATE(created\_date) FROM visits where file\_no = 80016 order by created\_date DESC LIMIT 0,1)));[Err] 1064 - 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 EXISTS(SELECT visit_id FROM visits WHERE file_no = 80016) THEN
BEGIN
SET' at line 1Technology News @ www.JassimRahma.com
-
Replace
Jassim Rahma wrote:
LIMIT 1)
with
LIMIT 0,1)
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
i have a feeling the problem is with the DATEDIFF but i don't know what is it..
Technology News @ www.JassimRahma.com
-
i have a feeling the problem is with the DATEDIFF but i don't know what is it..
Technology News @ www.JassimRahma.com
hmm I was right :) the problem is with the DATEDIFF but I still don't know what is it.. I just used TO_DAYS instead and everything is working fine now.
SET param_days_since_last_visit = (SELECT TO_DAYS(NOW()) - TO_DAYS(created_date) FROM visits WHERE file_no = param_file_no ORDER BY created_date DESC LIMIT 1);
Thanks, Jassim Rahma[^] RMC[^] - Universal Medical Equipments[^]
Technology News @ www.JassimRahma.com