SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Convert SQL Server Query to MySQL

    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
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  3. #3
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,810
    Mentioned
    141 Post(s)
    Tagged
    0 Thread(s)
    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
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  4. #4
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    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
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  5. #5
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,810
    Mentioned
    141 Post(s)
    Tagged
    0 Thread(s)
    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.
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  6. #6
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    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
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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 ;
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  9. #9
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,810
    Mentioned
    141 Post(s)
    Tagged
    0 Thread(s)
    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;
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by cms9651 View Post
    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?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    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'
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  12. #12
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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...
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  13. #13
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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:
    2013-01-07
    2013-01-08
    2013-01-09
    2013-01-10
    2013-01-11
    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 ();

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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 ();
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  18. #18
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,810
    Mentioned
    141 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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?
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  19. #19
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    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

  20. #20
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,810
    Mentioned
    141 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cms9651 View Post
    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.
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  21. #21
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    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
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by cpradio View Post
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •