Convert SQL Server Query to MySQL RSS

• • Reply
  

  cms9651

  Member

  184 Points

  599 Posts

  Convert SQL Server Query to MySQL

  Dec 27, 2012 01:30 PM|LINK

  Convert SQL Server Query to MySQL

  Hi there.
  What do I need to do to convert this query to work within in MySQL?
  
  Thank you for help.

  [SQL] DECLARE
  	@CountOfEmp INT = (SELECT COUNT(*) FROM `names`) WITH GroupsOfWeeks AS (
  		SELECT
  			*, n = 1 + (DATEPART(WEEK, Dates) - 1) %@CountOfEmp
  		FROM
  			tbl_dates
  	) SELECT
  		Dates,
  		DayDates,
  		`names`
  	FROM
  		GroupsOfWeeks
  	INNER JOIN `names` ON n = id
  	ORDER BY
  		Dates;
  [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 'DECLARE
  	@CountOfEmp INT = (SELECT COUNT(*) FROM `names`) WITH GroupsOfWeeks AS' at line 1

• • Reply
  

  bbcompent1

  All-Star

  33873 Points

  8776 Posts

  Moderator

  Re: Convert SQL Server Query to MySQL

  Dec 27, 2012 01:35 PM|LINK

  take a look here, seems like the @ shouldn't be there to declare variable: http://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference

  Mark as answer posts that helped you.
  
• • Reply
  

  cms9651

  Member

  184 Points

  599 Posts

  Re: Convert SQL Server Query to MySQL

  Dec 27, 2012 01:48 PM|LINK

  bbcompent1

  take a look here, seems like the @ shouldn't be there to declare variable: http://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference

  thank you.

  I tried this but I have error in output:

  mysql> DELIMITER //
  DROP PROCEDURE
  IF EXISTS prc_test //
  CREATE PROCEDURE prc_test ()
  BEGIN
  	DECLARE
  		CountOfEmp INT;
  SET CountOfEmp := (SELECT COUNT(*) FROM `names`);
  SELECT
  	CountOfEmp;
  
  WITH GroupsOfWeeks AS (
  	SELECT
  		*, n = 1 + (DATEPART(WEEK, Dates) - 1) %CountOfEmp
  	FROM
  		tbl_dates
  ) SELECT
  	Dates,
  	DayDates,
  	`names`
  FROM
  	GroupsOfWeeks
  INNER JOIN `names` ON n = id
  ORDER BY
  	Dates;
  
  END //
  DELIMITER;
  
  CALL prc_test ();
  Query OK, 0 rows affected
  
  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 'GroupsOfWeeks AS (
  	SELECT
  		*, n = 1 + (DATEPART(WEEK, Dates) - 1) %CountOfEm' at line 9

  
  
  

  • Edited by cms9651 on Dec 27, 2012 01:56 PM
  • Edited by cms9651 on Dec 27, 2012 02:12 PM
• • Reply
  

  bbcompent1

  All-Star

  33873 Points

  8776 Posts

  Moderator

  Re: Convert SQL Server Query to MySQL

  Dec 27, 2012 05:17 PM|LINK

  Instead of @ try using a ? instead.

  Mark as answer posts that helped you.
  
• • Reply
  

  cms9651

  Member

  184 Points

  599 Posts

  Re: Convert SQL Server Query to MySQL

  Dec 27, 2012 05:25 PM|LINK

  bbcompent1

  Instead of @ try using a ? instead.

  thank you.

  the problem is mysql doesn't support WITH syntax SQL Server.
  the CTE or common table expression (which is that whole thing defined by the WITH) can be written as a subquery.

  I tried this but I have error:

  [Err] 1054 - Unknown column 'n' in 'field list'

  My code:

  DROP PROCEDURE
  IF EXISTS prc_test;
  Affected rows: 0
  Time: 0.000ms
  
  [SQL] 
  
  CREATE PROCEDURE prc_test ()
  BEGIN
  	DECLARE
  		CountOfEmp INT ; SELECT
  			@CountOfEmp := COUNT(*)
  		FROM
  			NAMES ; SELECT
  				@CountOfEmp ; SELECT
  					Dates,
  					DayDates,
  					`names`
  				FROM
  					(
  						SELECT
  							*, n = 1 + (DATEPART(WEEK, Dates) - 1) %@CountOfEmp
  						FROM
  							`week`
  					) q
  				INNER JOIN `names` ON n = id
  				ORDER BY
  					Dates ;
  				END;
  Affected rows: 0
  Time: 0.000ms
  
  [SQL] 
  
  CALL prc_test ();
  [Err] 1054 - Unknown column 'n' in 'field list'

  
   

   

  • Edited by cms9651 on Dec 27, 2012 05:26 PM
  • Edited by cms9651 on Dec 27, 2012 05:42 PM
• • Reply
  

  bbcompent1

  All-Star

  33873 Points

  8776 Posts

  Moderator

  Re: Convert SQL Server Query to MySQL

  Dec 27, 2012 06:03 PM|LINK

  Found in though I can't speak for it: 
  http://www.dbforums.com/mysql/1627975-convert-sql-server-stored-procedure-into-mysql-stored-procedure.html

  "I am using SQLWAYS tool for evaluation purpose to convert all my SPs. Its working amazingly. http://www.ispirer.com/download/sqlways391905.zip "
  
  

  Mark as answer posts that helped you.
  
• • Reply
  

  bbcompent1

  All-Star

  33873 Points

  8776 Posts

  Moderator

  Re: Convert SQL Server Query to MySQL

  Dec 27, 2012 06:04 PM|LINK

  or you could use their express version: http://ispirer.com/request-sqlways-express 

  Mark as answer posts that helped you.
  
  • Marked as answer by cms9651 on Dec 27, 2012 07:28 PM
• • Reply
  

  cms9651

  Member

  184 Points

  599 Posts

  Re: Convert SQL Server Query to MySQL

  Dec 27, 2012 06:19 PM|LINK

  I understand the problem and found the solution, this version is working.
  Now the output in SQL server is 251 records, in mysql the output is 251 records.

  I change this line:

  1 + (DATEPART(week, Dates) - 1)

  to:

  1 + (EXTRACT(WEEK FROM `dates`))

  thank you.

  DELIMITER //
  DROP PROCEDURE
  IF EXISTS prc_test//
  
  CREATE PROCEDURE prc_test ()
  BEGIN
  DECLARE
  CountOfEmp INT ; SELECT
  @CountOfEmp := COUNT(*)
  FROM
  NAMES ; SELECT
  @CountOfEmp ; SELECT
  `dates`,
  `names`
  FROM
  (
  SELECT
  *, 1 + (EXTRACT(WEEK FROM `dates`)) %@CountOfEmp
  FROM
  `dates`
  ) AS GroupsOfWeeks
  INNER JOIN `names` ON 1 + (EXTRACT(WEEK FROM `dates`)) %@CountOfEmp = `names`.id
  ORDER BY
  `dates` ;
  END//
  
  DELIMITER ;
  
  
  CALL prc_test ();

  
  
  

  • Edited by cms9651 on Dec 27, 2012 07:28 PM