0

I am trying to create a query to return the number of working days in between 2 days but while trying to create a function it is giving below error.

ERROR 1064 (42000) at line 1: 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 '' at line 5

Kindly help me on this, this is the first ever MySQL function I wrote. Currently, I am unable to figure it out where I did wrong and also if there is any scope for improvement let me know. 

CREATE FUNCTION getNumOfDays (order_num_input INT)
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE total_days INT;

  SET total_days = 0;

  /* To get the id, end_date, actual_end_date and getting num of days between start date and end date of the table based on provided order number */
  SELECT case when datediff(end_date, start_date) = 0 then 1 else datediff(end_date, start_date) end INTO total_days
    FROM order
    WHERE order_num = order_num_input;


  RETURN total_days;
END
vvr02
  • 611
  • 2
  • 12
  • 22
  • Maybe helps a little, my syntax checker sais you have an error around `FROM order` in the first SELECT statement – JensV Jul 06 '17 at 12:07
  • I have removed into statements from the query to check it in sql query worked fine. – vvr02 Jul 06 '17 at 12:12
  • order is a keyword, you will have to escape it with backticks `\``, also these IF statements don't work like that in SQL, you have to rethink this and use SELECT to achive this. – JensV Jul 06 '17 at 12:17
  • You should start with an empty procedure and add one line after the other and execute it to test if it works (which is fine unless you change data inside your procedure). It currently has a lot of syntax errors, and it might help you to identify where these are and fix them. Since it will not be shown as an error, but will not work as you probably think it does, a warning: Be aware that (e.g.) `WHERE order_num = order_num;` will, unless it is `null`, always be true (like `1=1`, you are comparing a thing with itself). You might want to rename the variable to something that is not a column name. – Solarflare Jul 06 '17 at 12:44
  • I have corrected variable name to input. Simple function creation also not working seems, currently trying to check where it is being wrong. – vvr02 Jul 06 '17 at 12:49
  • Did you set a delimiter? If you do this e.g. in the workbench, you need to add e.g. `delimiter $$` (without `;` afterwards) in the line before your `create`, your last `END` will become `END $$` and after that you reset the delimiter with `delimiter ;`. So your starting point (empty function) could be `DROP FUNCTION IF EXISTS getNumOfWorkDays;` `DELIMITER $$` `CREATE FUNCTION getNumOfWorkDays (order_num_input INT) RETURNS INT BEGIN RETURN 1; END $$` `DELIMITER ;` `select getNumOfWorkDays(10);`. (I removed `deterministic`, as your function isn't deterministic). Then add new code and test. – Solarflare Jul 06 '17 at 15:02
  • Also I didn't want to imply that you have to figure out everything on your own now, so keep posting your code and error messages to get further help. It was just that your code had too many errors to list or find them all (and if we would suggest some fixes, you would still get error messages), while at the same time it's not possible to simply rewrite the function for you, as you could mean several things at some points. It's easier to solve more isolated problems, that preferable have a more concrete error message than "syntax to use near '' at line 5". – Solarflare Jul 06 '17 at 15:14
  • @Solarflare when i have tried to execute the simple function creation still getting the issue DROP FUNCTION IF EXISTS getNumOfWorkDays; DELIMITER $$ CREATE FUNCTION getNumOfWorkDays (order_num_input INT) RETURNS INT BEGIN RETURN 1; END $$ DELIMITER; ERROR 1064 (42000) at line 3: 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 'DELIMITER' at line 1 – vvr02 Jul 07 '17 at 03:01
  • @Solarflare thanks for the help i figured it out the basic mistake i was doing i was not giving the space between DELIMITER and semi column. Currently going step by step to resolve the issues. – vvr02 Jul 07 '17 at 03:24
  • @Solarflare provide you comments it as answer so that i can accept it. – vvr02 Jul 10 '17 at 04:38

0 Answers0