Convert SQL Server Query to MySQL

**cms9651** · Dec 27, 2012 03:28

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.

Code:

[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** · Dec 27, 2012 03:59

Code:

[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** · Dec 27, 2012 07:43

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** · Dec 27, 2012 07:54

Originally Posted by cpradio

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

thank you, I tried this:

Code:

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

And error is:

Code:

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** · Dec 27, 2012 08:24

http://forums.mysql.com/read.php?98,...979#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** · Dec 27, 2012 09:00

Originally Posted by cpradio

http://forums.mysql.com/read.php?98,...979#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.

thank you.

I tried this but I have error in output:

Code:

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

**r937** · Dec 27, 2012 09:55

mysql doesn't support WITH

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

Code:

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

SELECT @CountOfEmp ;

**cms9651** · Dec 27, 2012 10:15

thank you.

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

Code:

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.

Code:

mysql> 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** · Dec 27, 2012 10:18

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

Code:

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** · Dec 27, 2012 10:25

Originally Posted by cms9651

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

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** · Dec 27, 2012 10:48

Originally Posted by cpradio

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

Code:

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':

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'

**cms9651** · Dec 27, 2012 10:55

Originally Posted by r937

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?

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.

1. I need everyone to work an equal number of weeks (10 weeks for year);
2. Every week work is five consecutive working days;
3. 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** · Dec 27, 2012 13:18

this last version working also in MySQL.

Code:

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:

Code:

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

And start with:

Code:

Can you help me?

Code:

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** · Dec 27, 2012 13:37

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** · Dec 27, 2012 13:46

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** · Dec 27, 2012 13:50

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** · Dec 27, 2012 13:57

Originally Posted by r937

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?

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:

Code:

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

to:

Code:

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

thank you.

Code:

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** · Dec 27, 2012 14:06

Originally Posted by r937

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?

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** · Dec 27, 2012 14:16

Originally Posted by 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** · Dec 27, 2012 14:26

Originally Posted by cms9651

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

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.

**cms9651** · Dec 28, 2012 04:31

Originally Posted by cpradio

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.

if over time I learned something about MySql is a credit to this forum and everyone who contributed.
starting with you cpradio and r937
thank you for your patience
goodbye

**r937** · Dec 28, 2012 13:06

Originally Posted by 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

yes, that is true

User Tag List

Thread: Convert SQL Server Query to MySQL

Thread Tools

Display

Convert SQL Server Query to MySQL

Bookmarks

Bookmarks

Posting Permissions