Error 1064 When Transferring Access SQL Proceedure to MySQL 1

[spmaslin]

Hi,

I am trying to develop a data model in MySQL using SQL code taken directly from an MS Access database (where it works fine). The Access version is 2002 with SP-2 in Windows XP and the MySQL version is 4.0.18-nt.

Problem is, the query routines (which are unaltered Access-generated SQL) don't work in MySQL. I am using two types of pre-existing tables for these proceedures, ones prefixed with 'Data_' contain data and ones prefixed with 'Run_' which are initially blank and intended for use as temporary batch tables to be filled as the proceedure runs.

The model is built around queries such as the following:

SELECT Data_AllPosterData.PSG, Count(Data_AllPosterData.ID) AS GrossOTS, Sum(Data_AllPosterData.Visibility) AS NetOTS, Sum(Data_AllPosterData.Invisibility) AS Invisibility INTO Run_ActivePSGs
FROM Data_AllPosterData INNER JOIN Data_TestPacks ON Data_AllPosterData.PosterID = Data_TestPacks.PosterID
WHERE (((Data_TestPacks.TestPackID)=632))
GROUP BY Data_AllPosterData.PSG
HAVING (((Sum(Data_AllPosterData.Visibility)) Is Not Null Or (Sum(Data_AllPosterData.Visibility))<>0));

However, for every one of the routines, the error message is:

"ERROR 1064: You have and error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'Run_ActivePSGs FROM Data_AllPosterData INNER JOIN Data_TestPac"

As these routines work fine in Access, the tables they relate to exist in the MySQL database and have the correct fields/data types defined, I am at a loss to explain this. There is nothing in the manual relating to this error in this form.

Please Help!!!!

 

[TonyGroves]

SELECT ... INTO can't be used to create temporary tables.
You could rewrite as follows:
[tt]
CREATE TEMPORARY TABLE Run_ActivePSGs AS
SELECT
Data_AllPosterData.PSG,
COUNT(Data_AllPosterData.ID) AS GrossOTS,
SUM(Data_AllPosterData.Visibility) AS NetOTS,
SUM(Data_AllPosterData.Invisibility) AS Invisibility
FROM
Data_AllPosterData INNER JOIN Data_TestPacks
ON Data_AllPosterData.PosterID = Data_TestPacks.PosterID
WHERE Data_TestPacks.TestPackID=632
GROUP BY Data_AllPosterData.PSG HAVING NetOTS IS NOT NULL OR NetOTS<>0
[/tt]
By the way, is that a logic error in your HAVING clause?
[tt]HAVING (((Sum(Data_AllPosterData.Visibility)) Is Not Null
Or (Sum(Data_AllPosterData.Visibility))<>0))[/tt]
would include all non-null values of Sum(Data_AllPosterData.Visibility), making the [tt]OR Sum(Data_AllPosterData.Visibility)<>0[/tt] redundant.

 

[TonyGroves]

... or, slightly simpler:
[tt]
CREATE TEMPORARY TABLE Run_ActivePSGs AS
SELECT
Data_AllPosterData.PSG,
COUNT(Data_AllPosterData.ID) AS GrossOTS,
SUM(Data_AllPosterData.Visibility) AS NetOTS,
SUM(Data_AllPosterData.Invisibility) AS Invisibility
FROM
Data_AllPosterData JOIN Data_TestPacks USING PosterID
WHERE Data_TestPacks.TestPackID=632
GROUP BY Data_AllPosterData.PSG HAVING NetOTS IS NOT NULL OR NetOTS<>0
[/tt]

 

[spmaslin]

Thanks for those swift replies! Of course you're right - select into doesn't work in this situation. I will try your responses...

Thanks!

 

[TonyGroves]

Re-reading your question, am I right in thinking that what you want is to run the query several times using different values of TestPackId, adding the result to the temporary table each time?

If so, then the following should work (after creating the table):
[tt]
INSERT Run_ActivePSGs
SELECT
Data_AllPosterData.PSG,
COUNT(Data_AllPosterData.ID) AS GrossOTS,
SUM(Data_AllPosterData.Visibility) AS NetOTS,
SUM(Data_AllPosterData.Invisibility) AS Invisibility
FROM
Data_AllPosterData INNER JOIN Data_TestPacks
ON Data_AllPosterData.PosterID = Data_TestPacks.PosterID
WHERE Data_TestPacks.TestPackID=632
GROUP BY Data_AllPosterData.PSG HAVING NetOTS IS NOT NULL OR NetOTS<>0
[/tt]

 

[spmaslin]

Thanks - am trying your suggestions!

Regards,

Simon