• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

Convert sp from MS SQL to MySQL (5.0)

Can anyone help me convert a stored procedure  that includes a CTE from MS SQL to MySQL 5.0?  
WITH CompanyListings AS 
	(
	SELECT
		T1.ComanyID, T1.CompanyName, T1.CompanyURL, T1.School, T2.DisplayAlways, 
		MAX(T3.SessionEndDate) OVER (PARTITION BY T1.ComanyID) AS DisplayDate
	FROM TrainingCompanies T1
	LEFT JOIN TrainingCourses T2 ON T1.ComanyID = T2.Course_CompanyID
	LEFT JOIN TrainingSessions T3 ON T2.CourseID = T3.Session_CourseID	
	)
SELECT DISTINCT 
	ComanyID, CompanyName, CompanyURL, ISNULL(School,'') AS School 
FROM CompanyListings
WHERE DisplayDate > DATEADD(D,-3, GETDATE()) OR DisplayAlways = 1
ORDER BY CompanyName

Open in new window

0
cgcmq
Asked:
cgcmq
  • 5
  • 4
1 Solution
 
Aaron ShiloChief Database ArchitectCommented:
SELECT DISTINCT 
	ComanyID, CompanyName, CompanyURL, ISNULL(School,'') AS School 
FROM (
	SELECT
		T1.ComanyID, T1.CompanyName, T1.CompanyURL, T1.School, T2.DisplayAlways, 
		MAX(T3.SessionEndDate) OVER (PARTITION BY T1.ComanyID) AS DisplayDate
	FROM TrainingCompanies T1
	LEFT JOIN TrainingCourses T2 ON T1.ComanyID = T2.Course_CompanyID
	LEFT JOIN TrainingSessions T3 ON T2.CourseID = T3.Session_CourseID	
	) CompanyListings 
WHERE DisplayDate > DATEADD(D,-3, GETDATE()) OR DisplayAlways = 1
ORDER BY CompanyName 

Open in new window

0
 
cgcmqAuthor Commented:
Are you certain that your solution is MySQL?  It looks like an MS SQL query.
0
 
Aaron ShiloChief Database ArchitectCommented:
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
cgcmqAuthor Commented:
Sorry - I don't work with MySQL much & do not know the query syntax very well.  When I cut & pasted the query & ran it (using Navicat) I received the following error:

1064 - You have an error in your SQL syntax... near '') AS School
FROM (
      SELECT
0
 
Aaron ShiloChief Database ArchitectCommented:
try this


SELECT DISTINCT 
	ComanyID, CompanyName, CompanyURL, IfNULL(School,'') AS School 
FROM (
	SELECT
		T1.ComanyID, T1.CompanyName, T1.CompanyURL, T1.School, T2.DisplayAlways, 
		MAX(T3.SessionEndDate) OVER (PARTITION BY T1.ComanyID) AS DisplayDate
	FROM TrainingCompanies T1
	LEFT JOIN TrainingCourses T2 ON T1.ComanyID = T2.Course_CompanyID
	LEFT JOIN TrainingSessions T3 ON T2.CourseID = T3.Session_CourseID	
	) CompanyListings 
WHERE DisplayDate > DATEADD(D,-3, CURDATE())OR DisplayAlways = 1
ORDER BY CompanyName  

Open in new window

0
 
cgcmqAuthor Commented:
That solved the initial error - thank you.  

It now stumbles at:
  (PARTITION BY T1.ComanyID) AS DisplayDate
      FROM TrainingCompanies T1
0
 
Aaron ShiloChief Database ArchitectCommented:
0
 
cgcmqAuthor Commented:
Like I said, I am not familiar with MySQL so it is going to take me some time to go through this.  I will get back to you shortly.
0
 
cgcmqAuthor Commented:
I downloaded a syntax converter (SwissSQL Console) and it gave me an output very similar to what you had which was of limited use as it clearly is not functional MySQL.

Instead of following the example at Xaprb, I re-wrote the query in MS SQL so that it did not use a CTE or 'partition over'.  From that I was able to more easily convert it to MySQL.

      SELECT DISTINCT
                   ComanyID,
                   CompanyName,
                   CompanyURL,
                   COALESCE(School, '') AS School
      FROM TrainingCompanies T1
      LEFT JOIN TrainingCourses T2 ON T1.ComanyID = T2.Course_CompanyID
      WHERE CourseID IN
            (
            SELECT Session_CourseID
            FROM TrainingSessions
            WHERE SessionEndDate > DATE_ADD((CURRENT_TIMESTAMP), INTERVAL  - 3  DAY)
            ) OR DisplayAlways  = 1

ORDER BY CompanyName
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now