Temp Table and stored procedure issues...

Hi,

I'm a recent convert from MS SQL so I'm still trying to get used to differences in syntax, etc with MySQL.  As such, I'm at the point now where I need to start using Temporary tables in stored procedures.  I've tried a number of different combinations, but I can't get multiple selects into one temporary table.  The following code simply doesn't execute.  Also, can I

First the code then the error messages:

create procedure spAlert()

create temporary table tempAlert(MeasurementShort varchar(30),Sensor varchar(20),SensorData float,SensorSeq int,
                                  SensorDataLastUpdateDate datetime,MinValue float,MaxValue float,EventType varchar(20),
                                  EventDescription varchar(50), EventLastUpdateDate datetime)

select  MeasurementShort,Sensor,SensorData,Sensor.SensorSeq,SensorData.LastUpdateDate as SensorDataLastUpdateDate,MinValue,MaxValue
into    tempAlert
from    SensorData,Sensor,Measurement,user,userpreferences
where  SensorData.SensorSeq = Sensor.SensorSeq AND
           SensorData.MeasurementSeq = Measurement.MeasurementSeq AND
           Measurement.Measurement NOT IN ('SampleDate','SampleTime') AND
           (SensorData.LastUpdateDate > '2006-03-20 10:06:30' AND SensorData.LastUpdateDate < '2006-03-21 10:06:30') AND
           measurement.measurementseq = userpreferences.measurementseq AND user.userseq = userpreferences.userseq AND
           user.username = 'jricha' AND (SensorData.SensorData < userpreferences.MinValue OR
           SensorData.SensorData > userpreferences.MaxValue)

update tempAlert
set EventType = 'Warning'

insert into tempAlert (EventType,EventDescription,EventLastUpdateDate)
select EventType,Description,LastUpdateDate
from Event
where (Event.LastUpdateDate > '2006-03-20 10:06:30' AND Event.LastUpdateDate < '2006-03-21 10:06:30')

select * from tempAlert

Errors:
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 'into      new_alert
from    SensorData,Sensor,Measurement,user,userpreferences
' at line 6

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 'create temporary table tempAlert(MeasurementShort varchar(30),Sensor varchar(20)' at line 1
blue44Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

todd_farmerCommented:
Try changing it to use INSERT INTO ... SELECT syntax.
0
todd_farmerCommented:
So....

INSERT INTO tempAlert SELECT
select  MeasurementShort,Sensor,SensorData,Sensor.SensorSeq,SensorData.LastUpdateDate as SensorDataLastUpdateDate,MinValue,MaxValue
FROM   SensorData,Sensor,Measurement,user,userpreferences
WHERE  SensorData.SensorSeq = Sensor.SensorSeq AND
           SensorData.MeasurementSeq = Measurement.MeasurementSeq AND
           Measurement.Measurement NOT IN ('SampleDate','SampleTime') AND
           (SensorData.LastUpdateDate > '2006-03-20 10:06:30' AND SensorData.LastUpdateDate < '2006-03-21 10:06:30') AND
           measurement.measurementseq = userpreferences.measurementseq AND user.userseq = userpreferences.userseq AND
           user.username = 'jricha' AND (SensorData.SensorData < userpreferences.MinValue OR
           SensorData.SensorData > userpreferences.MaxValue)
0
blue44Author Commented:
Thanks...now I get these error messages:

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 'into      new_alert
from    SensorData,Sensor,Measurement,user,userpreferences
' at line 6

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 'create procedure spAlert()

create temporary table tempAlert(MeasurementShort ' at line 1
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

todd_farmerCommented:
Sorry, I had a duplicate SELECT keyword in my last post.  Also make sure you are using the semi-colon:

INSERT INTO tempAlert
SELECT  MeasurementShort,Sensor,SensorData,Sensor.SensorSeq,SensorData.LastUpdateDate as SensorDataLastUpdateDate,MinValue,MaxValue
FROM   SensorData,Sensor,Measurement,user,userpreferences
WHERE  SensorData.SensorSeq = Sensor.SensorSeq AND
           SensorData.MeasurementSeq = Measurement.MeasurementSeq AND
           Measurement.Measurement NOT IN ('SampleDate','SampleTime') AND
           (SensorData.LastUpdateDate > '2006-03-20 10:06:30' AND SensorData.LastUpdateDate < '2006-03-21 10:06:30') AND
           measurement.measurementseq = userpreferences.measurementseq AND user.userseq = userpreferences.userseq AND
           user.username = 'jricha' AND (SensorData.SensorData < userpreferences.MinValue OR
           SensorData.SensorData > userpreferences.MaxValue);

0
blue44Author Commented:
Thanks...new error message:

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 'INSERT INTO tempAlert
SELECT  MeasurementShort,Sensor,SensorData,Sensor.SensorS' at line 7

Here's my full code again in case I'm missing any other semi-colons or the like:

create procedure spAlert()

create temporary table tempAlert(MeasurementShort varchar(30),Sensor varchar(20),SensorData float,SensorSeq int,
                                  SensorDataLastUpdateDate datetime,MinValue float,MaxValue float,EventType varchar(20),
                                  EventDescription varchar(50), EventLastUpdateDate datetime)

INSERT INTO tempAlert
SELECT  MeasurementShort,Sensor,SensorData,Sensor.SensorSeq,SensorData.LastUpdateDate as SensorDataLastUpdateDate,MinValue,MaxValue
FROM   SensorData,Sensor,Measurement,user,userpreferences
WHERE  SensorData.SensorSeq = Sensor.SensorSeq AND
           SensorData.MeasurementSeq = Measurement.MeasurementSeq AND
           Measurement.Measurement NOT IN ('SampleDate','SampleTime') AND
           (SensorData.LastUpdateDate > '2006-03-20 10:06:30' AND SensorData.LastUpdateDate < '2006-03-21 10:06:30') AND
           measurement.measurementseq = userpreferences.measurementseq AND user.userseq = userpreferences.userseq AND
           user.username = 'jricha' AND (SensorData.SensorData < userpreferences.MinValue OR
           SensorData.SensorData > userpreferences.MaxValue);

update tempAlert
set EventType = 'Warning'

insert into tempAlert (EventType,EventDescription,EventLastUpdateDate)
select EventType,Description,LastUpdateDate
from Event
where (Event.LastUpdateDate > '2006-03-20 10:06:30' AND Event.LastUpdateDate < '2006-03-21 10:06:30')

select * from tempAlert
0
todd_farmerCommented:
Try this:

SET DELIMITER ||
create procedure spAlert()
BEGIN
create temporary table tempAlert(MeasurementShort varchar(30),Sensor varchar(20),SensorData float,SensorSeq int,
                                  SensorDataLastUpdateDate datetime,MinValue float,MaxValue float,EventType varchar(20),
                                  EventDescription varchar(50), EventLastUpdateDate datetime);

INSERT INTO tempAlert
SELECT  MeasurementShort,Sensor,SensorData,Sensor.SensorSeq,SensorData.LastUpdateDate as SensorDataLastUpdateDate,MinValue,MaxValue
FROM   SensorData,Sensor,Measurement,user,userpreferences
WHERE  SensorData.SensorSeq = Sensor.SensorSeq AND
           SensorData.MeasurementSeq = Measurement.MeasurementSeq AND
           Measurement.Measurement NOT IN ('SampleDate','SampleTime') AND
           (SensorData.LastUpdateDate > '2006-03-20 10:06:30' AND SensorData.LastUpdateDate < '2006-03-21 10:06:30') AND
           measurement.measurementseq = userpreferences.measurementseq AND user.userseq = userpreferences.userseq AND
           user.username = 'jricha' AND (SensorData.SensorData < userpreferences.MinValue OR
           SensorData.SensorData > userpreferences.MaxValue);

update tempAlert
set EventType = 'Warning';

insert into tempAlert (EventType,EventDescription,EventLastUpdateDate)
select EventType,Description,LastUpdateDate
from Event
where (Event.LastUpdateDate > '2006-03-20 10:06:30' AND Event.LastUpdateDate < '2006-03-21 10:06:30');

select * from tempAlert;
END
||

DELIMITER ;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
blue44Author Commented:
Don't hate me, but here's another error code...

Unknown system variable 'DELIMITER'
0
todd_farmerCommented:
Don't hate you!  I'm the one giving bad syntax!  LOL!

The very first line should be:

DELIMITER ||

instead of:

SET DELIMITER ||
0
blue44Author Commented:
cool...that created the sp but after running the sp, I got the following error:

Column count doesn't match value count at row 1

So, I went to run the query by itself, but the Query browser doesn't let me execute all statements at once...so it breaks each statement up and gives a corresponding error message:

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 'create temporary table tempAlert(MeasurementShort varchar(30),Sensor varchar(20)' at line 2

Here's my entire query:
BEGIN
create temporary table tempAlert(MeasurementShort varchar(30),Sensor varchar(20),SensorData float,SensorSeq int,
                                  SensorDataLastUpdateDate datetime,MinValue float,MaxValue float,EventType varchar(20),
                                  EventDescription varchar(50), EventLastUpdateDate datetime);

INSERT INTO tempAlert
SELECT  MeasurementShort,Sensor,SensorData,Sensor.SensorSeq,SensorData.LastUpdateDate as SensorDataLastUpdateDate,MinValue,MaxValue
FROM   SensorData,Sensor,Measurement,user,userpreferences
WHERE  SensorData.SensorSeq = Sensor.SensorSeq AND
           SensorData.MeasurementSeq = Measurement.MeasurementSeq AND
           Measurement.Measurement NOT IN ('SampleDate','SampleTime') AND
           (SensorData.LastUpdateDate > '2006-03-20 10:06:30' AND SensorData.LastUpdateDate < '2006-03-21 10:06:30') AND
           measurement.measurementseq = userpreferences.measurementseq AND user.userseq = userpreferences.userseq AND
           user.username = 'jricha' AND (SensorData.SensorData < userpreferences.MinValue OR
           SensorData.SensorData > userpreferences.MaxValue);

update tempAlert
set EventType = 'Warning';

insert into tempAlert (EventType,EventDescription,EventLastUpdateDate)
select EventType,Description,LastUpdateDate
from Event
where (Event.LastUpdateDate > '2006-03-20 10:06:30' AND Event.LastUpdateDate < '2006-03-21 10:06:30');

select * from tempAlert;
END



0
todd_farmerCommented:
Skip the BEGIN and END statements when trying to execute the SQL statements directly (not in the context of the stored procedure definition).
0
blue44Author Commented:
OK...that worked, but I still have a problem with the SQL...it gives me the following error message: "Column count doesn't match value count at row 1" when executing the following portion:

INSERT INTO tempAlert
SELECT  MeasurementShort,Sensor,SensorData,Sensor.SensorSeq,SensorData.LastUpdateDate as SensorDataLastUpdateDate,MinValue,MaxValue
FROM   SensorData,Sensor,Measurement,user,userpreferences
WHERE  SensorData.SensorSeq = Sensor.SensorSeq AND
           SensorData.MeasurementSeq = Measurement.MeasurementSeq AND
           Measurement.Measurement NOT IN ('SampleDate','SampleTime') AND
           (SensorData.LastUpdateDate > '2006-03-20 10:06:30' AND SensorData.LastUpdateDate < '2006-03-21 10:06:30') AND
           measurement.measurementseq = userpreferences.measurementseq AND user.userseq = userpreferences.userseq AND
           user.username = 'jricha' AND (SensorData.SensorData < userpreferences.MinValue OR
           SensorData.SensorData > userpreferences.MaxValue);

I believe the reason for this is that my temp table definition specifies 10 columns and this select only populates 7 columns.  The remaining 3 columns are updated in the next statement.  Do you know how I can get around this?

I've increased the point total to 500 in hope you'll see me through this process.  Many thanks!!
0
blue44Author Commented:
Hi...Are you still interested in helping me with this issue? If not, I'll award you some points and re-post the question.  Please let me know ASAP.  Don't mean to pressure you, but I'm under a deadline.

Many thanks!
0
todd_farmerCommented:
Yes - thanks for bringing this to my attention.  See if this works for you:

BEGIN
create temporary table tempAlert(MeasurementShort varchar(30),Sensor varchar(20),SensorData float,SensorSeq int,
                                  SensorDataLastUpdateDate datetime,MinValue float,MaxValue float,EventType varchar(20),
                                  EventDescription varchar(50), EventLastUpdateDate datetime);

INSERT INTO tempAlert (MeasureMentShort, Sensor, SensorData, SensorSeq, SensorDataLastUpdateDate, MinValue, MaxValue)
SELECT  MeasurementShort,Sensor,SensorData,Sensor.SensorSeq,SensorData.LastUpdateDate as SensorDataLastUpdateDate,MinValue,MaxValue
FROM   SensorData,Sensor,Measurement,user,userpreferences
WHERE  SensorData.SensorSeq = Sensor.SensorSeq AND
           SensorData.MeasurementSeq = Measurement.MeasurementSeq AND
           Measurement.Measurement NOT IN ('SampleDate','SampleTime') AND
           (SensorData.LastUpdateDate > '2006-03-20 10:06:30' AND SensorData.LastUpdateDate < '2006-03-21 10:06:30') AND
           measurement.measurementseq = userpreferences.measurementseq AND user.userseq = userpreferences.userseq AND
           user.username = 'jricha' AND (SensorData.SensorData < userpreferences.MinValue OR
           SensorData.SensorData > userpreferences.MaxValue);

update tempAlert
set EventType = 'Warning';

insert into tempAlert (EventType,EventDescription,EventLastUpdateDate)
select EventType,Description,LastUpdateDate
from Event
where (Event.LastUpdateDate > '2006-03-20 10:06:30' AND Event.LastUpdateDate < '2006-03-21 10:06:30');

select * from tempAlert;
END


Note also that I'm not sure what you are intending to do with the last INSERT statement.  This will create new rows in the tempAlert table rather than update the existing rows.  Is this what you mean to do?
0
blue44Author Commented:
Thanks for getting back to me, Todd!!! I ended up figuring out a solution by adding columns to the temp table before making the second insert.  In any case, I'm going to reward 400 points for helping me get the rest of the important sections working.  I'm sure I will have more questions in the future and hopefully you'll be around to help again.

Darius
0
blue44Author Commented:
Ooops...500 points...my mistake...no worries...:-)
0
todd_farmerCommented:
Good deal - thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

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.