Convert SQL Server Query to MySQL

cms9651 · December 27, 2012, 9:28am

Convert SQL Server Query to MySQL
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
        )


;
[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
                  ' at line 1

cms9651 · December 27, 2012, 9:59am

[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

cpradio · December 27, 2012, 1:43pm

From what I can tell, you are trying to use DECLARE incorrectly.

First, you need to use a cursor
Second, DECLARE must be in a BEGIN/END statement

cms9651 · December 27, 2012, 1:54pm

thank you, I tried this:

BEGIN
DECLARE CountOfEmp CURSOR FOR SELECT COUNT(*) FROM `names`;
OPEN CountOfEmp;
CLOSE CountOfEmp;
END

And error is:

BEGIN
DECLARE CountOfEmp CURSOR FOR SELECT COUNT(*) FROM `names`;
[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 CURSOR FOR SELECT COUNT(*) FROM `names`' at line 21

cpradio · December 27, 2012, 2:24pm

http://forums.mysql.com/read.php?98,135972,135979#msg-135979

The above URL is utilizing DELIMITER, although I have no idea why that may be necessary, let alone when. Sorry, I’m not 100% sure what the issue could be at this point.

cms9651 · December 27, 2012, 3:00pm

thank you.

I tried this but I have error in output:


mysql&gt; 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

r937 · December 27, 2012, 3:55pm

mysql doesn’t support WITH

why don’t you use a mysql variable instead of ramming sql server syntax down its throat?

SELECT @CountOfEmp := COUNT(*) FROM names;

SELECT @CountOfEmp ;

cms9651 · December 27, 2012, 4:15pm

thank you.

My problem is convert this part of code SQL Server query to MySQL:

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;

because the variable is correct value in the sp.

mysql&gt; DELIMITER //
DROP PROCEDURE
IF EXISTS prc_test //
CREATE PROCEDURE prc_test ()
BEGIN
	DECLARE
		CountOfEmp INT;
SELECT @CountOfEmp := COUNT(*) FROM names;
SELECT @CountOfEmp ;
END //
DELIMITER;

CALL prc_test ();
Query OK, 0 rows affected

Query OK, 0 rows affected

+-------------------------+
| @CountOfEmp := COUNT(*) |
+-------------------------+
|                       5 |
+-------------------------+
1 row in set

+-------------+
| @CountOfEmp |
+-------------+
|           5 |
+-------------+
1 row in set

Query OK, 0 rows affected

cpradio · December 27, 2012, 4:18pm

I might be wrong, but wouldn’t that be the same as

SELECT
	Dates,
	DayDates,
	`names`
FROM
	(SELECT
		*, n = 1 + (DATEPART(WEEK, Dates) - 1) %@CountOfEmp
	FROM
		tbl_dates)
INNER JOIN `names` ON n = id
ORDER BY
	Dates;

r937 · December 27, 2012, 4:25pm

the CTE or common table expression(which is that whole thing defined by the WITH) can be written as a subquery

my trouble is understanding what you’re trying to do, and i can’t figure that out by looking at bad syntax

what is the purpose of “groups of weeks” anyway?

cms9651 · December 27, 2012, 4:48pm

cpradio:

I might be wrong, but wouldn’t that be the same as

SELECT
	Dates,
	DayDates,
	`names`
FROM
	(SELECT
		*, n = 1 + (DATEPART(WEEK, Dates) - 1) %@CountOfEmp
	FROM
		tbl_dates)
INNER JOIN `names` ON n = id
ORDER BY
	Dates;

thank you for help.

But, [Err] 1054 - Unknown column ‘n’ in ‘field list’:

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'

cms9651 · December 27, 2012, 4:55pm

In my case I counted 50 weeks in a year divided by 5 workers = 10 weeks each because for two week for year is vacation.

I need everyone to work an equal number of weeks (10 weeks for year);
Every week work is five consecutive working days;
Every Monday I need change the name of employee;

I find the solution for db SQL Server now I need convert SQL Server Query to MySQL…

cms9651 · December 27, 2012, 7:18pm

this last version working also in MySQL.


EXTRACT(unit FROM date) Syntax for MySQL
DATEPART(week, Dates)   Syntax for SQL Server.

but if tried in SQL server the output are 251 records, tried in mysql the output are 248 records.

I don’t understand why the sp in MySQL excludes the first three dates of the table:


2013-01-02
2013-01-03
2013-01-04

And start with:

Can you help me?


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) - 1) %@CountOfEmp
						FROM
							`dates`
					) AS GroupsOfWeeks
				INNER JOIN `names` a ON 1 + (EXTRACT(WEEK FROM dates) - 1) %@CountOfEmp = a.id
				ORDER BY
					Dates ;
				END//

DELIMITER ;

CALL prc_test ();

r937 · December 27, 2012, 7:37pm

i think you’ve been working on this problem for how long, a few weeks?

just sit down with a calendar and assign the employees for a couple of years yourself

(it’s what, 50 or so entries per year? i could do a decade of weekly assignments in about 5 minutes)

then load that stuff up into your database and you’re finished

cms9651 · December 27, 2012, 7:46pm

you have certainly right… but the problem is my teacher of relational database management system…
I have to submit this examination for version SQL Server and version for MYSQL

r937 · December 27, 2012, 7:50pm

i can’t remember, but in all of these threads on various forums that you have posted, did you ever mention that it was a school assignment when you asked people to solve your problems?

cms9651 · December 27, 2012, 7:57pm

this is the first case and I did not because it is shame for me, I’m sorry
other discussions concerning personal experiments nothing to do with the school assignment.

however, 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 ();

cpradio · December 27, 2012, 8:06pm

To be fair though, I do appreciate that cms9651 has consistently shown his attempts at solving said problems instead of just asking for answers (granted this particular thread doesn’t exactly fit that first statement, but he did later get himself to a closer working solution).

@cms9651; , I’m not 100% certain, as I really think there is missing information that I would need to make this assessment, but my guess is because the first of January is on a Tuesday (the first business day being Wednesday), it is excluding the first work week in 2013 and starting with the first full work week.

Since I am uncertain with how your logic is determining what is considered the first work week of a year, let alone, how it is filtering down to only show dates from 2013, I can’t tell where this error is located within your procedure. Can you provide more information or possibly look into what in your project may be excluding the first week of 2013 since it starts on a Wednesday and not on Monday?

cms9651 · December 27, 2012, 8:16pm

cpradio:

To be fair though, I do appreciate that cms9651 has consistently shown his attempts at solving said problems instead of just asking for answers (granted this particular thread doesn’t exactly fit that first statement, but he did later get himself to a closer working solution).

@cms9651; , I’m not 100% certain, as I really think there is missing information that I would need to make this assessment, but my guess is because the first of January is on a Tuesday (the first business day being Wednesday), it is excluding the first work week in 2013 and starting with the first full work week.

Since I am uncertain with how your logic is determining what is considered the first work week of a year, let alone, how it is filtering down to only show dates from 2013, I can’t tell where this error is located within your procedure. Can you provide more information or possibly look into what in your project may be excluding the first week of 2013 since it starts on a Wednesday and not on Monday?

thanks for your words.
but it is not always easy to explain the problems found.
however, this problem is closed: see post #17 Today, 12:57
Good bye

cpradio · December 27, 2012, 8:26pm

Dope!, I missed the fact that you posted that. Cool, glad it was something simple, those are always the hardest to usually track down, but its great knowing you don’t have to rework the entire query.