MySql MariaDB create Procedure error

Question

I am building my first mySQL database for a project, transfering a database from MS Access to mysql. Now I know that I have a huge learning curve but that is okay, I have to learn it one day.

I am building this database in the mySQL database of XAMPP-phpMyAdmin.

The problem I am having is on creating a procedure, here is the full procedure: (some of the table or variable names have been changed due to confidental nature of my work, so no need to comment that Table1 is a bad name etc.., I already know)

DELIMITER $$

DROP PROCEDURE IF EXISTS Proc1$$

CREATE PROCEDURE Proc1(IN pType INT)

BEGIN

SET @runningTotal=0, @prevTotal=0, @Aim=5, @Period=21;

DROP TEMPORARY TABLE IF EXISTS temp_table;


CREATE TEMPORARY TABLE IF NOT EXISTS temp_table(
  `id` int(11) NOT NULL,
  `dDate` date DEFAULT NULL,
  `type` int(11) DEFAULT NULL,
  `MyAmountCol` int(11) DEFAULT NULL
);

INSERT INTO temp_table(`id`, `dDate`, `type`, `MyAmountCol`) 
  SELECT `id`, `dDate`, `type`, `MyAmountCol` 
  FROM Table1 
  WHERE type=pType AND 
        table1.dDate>=DATE_SUB(CURDATE(), INTERVAL @Period DAY) AND
        table1.dDate!=CURDATE();

DROP TEMPORARY TABLE IF EXISTS temp_table_result;

CREATE TEMPORARY TABLE IF NOT EXISTS temp_table_result AS
  SELECT
    temp_table.Id,
    temp_table.dDate,
    temp_table.MyAmountCol,
    DATE_ADD(temp_table.dDate, INTERVAL @Period DAY) as 'RecDay',
    DATE_ADD(@prevDate, INTERVAL @Period DAY) As 'NextRecDay',
    @prevTotal := @runningTotal as 'PreviousTotal',
    @Aim -@prevTotal as 'MinAmmount',
    @runningTotal := @runningTotal + temp_table.MyAmountCol as 'RunningTotal',
    @prevDate := temp_table.dDate
    pType as 'Type'
  FROM temp_table 
  HAVING PreviousTotal <= @Aim
  ORDER BY RunningTotal DESC LIMIT 1;

UPDATE Result A INNER JOIN temp_table_result B (A.type = B.Type) 
  SET  A.RecDay=B.RecDay, A.NextRecDay=B.NextRecDay, A.MinHours=B.MinAmmount;

DROP TEMPORARY TABLE IF EXISTS temp_table;
DROP TEMPORARY TABLE IF EXISTS temp_table_result;

END;
$$
DELIMITER ;

So the problem is I have spent all day looking at brings up the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'as 'Type'
FROM temp_table 
HAVING PreviousTotal <= @Aim
ORDER BY Runn' at line 37

I know it is a syntax error and I have been slowly fixing the syntax errors one by one, (the joys of learning as you create). But I can't figure out this syntax error.

Thanks for all your help

Get rid of both temp tables. Get rid of temp_table_result by using SELECT ... INTO .... Get rid of temp_table by making it a 'derived' table: ... FROM ( SELECT ... ) AS temp_table ... — Rick James
– Rick James, Commented Apr 1, 2017 at 19:49
@RickJames will try that when I am next in the db, I thought procedures couldn't have sub queries? Or do I need to go back to my research and read again! — Bullfrog
– Bullfrog, Commented Apr 1, 2017 at 20:03

Sagar Gangwal · Accepted Answer · 2017-03-31 09:03:17Z

2

Just try below code. Hope this will helps.

DROP PROCEDURE IF EXISTS Proc1;
DELIMITER $$
CREATE PROCEDURE Proc1(IN pType INT)

BEGIN

SET @runningTotal=0, @prevTotal=0, @Aim=5, @Period=21;

DROP TEMPORARY TABLE IF EXISTS temp_table;


CREATE TEMPORARY TABLE IF NOT EXISTS temp_table(
  `id` int(11) NOT NULL,
  `dDate` date DEFAULT NULL,
  `type` int(11) DEFAULT NULL,
  `MyAmountCol` int(11) DEFAULT NULL
);

INSERT INTO temp_table(`id`, `dDate`, `type`, `MyAmountCol`) 
  SELECT `id`, `dDate`, `type`, `MyAmountCol` 
  FROM Table1 
  WHERE type=pType AND 
        table1.dDate>=DATE_SUB(CURDATE(), INTERVAL @Period DAY) AND
        table1.dDate!=CURDATE();

DROP TEMPORARY TABLE IF EXISTS temp_table_result;

CREATE TEMPORARY TABLE IF NOT EXISTS temp_table_result AS
  SELECT
    temp_table.Id,
    temp_table.dDate,
    temp_table.MyAmountCol,
    DATE_ADD(temp_table.dDate, INTERVAL @Period DAY) as 'RecDay',
    DATE_ADD(@prevDate, INTERVAL @Period DAY) As 'NextRecDay',
    @prevTotal := @runningTotal as 'PreviousTotal',
    @Aim -@prevTotal as 'MinAmmount',
    @runningTotal := @runningTotal + temp_table.MyAmountCol as 'RunningTotal',
    @prevDate := temp_table.dDate, <--- Make change at this line.
    pType as 'Type'
  FROM temp_table 
  HAVING PreviousTotal <= @Aim
  ORDER BY RunningTotal DESC LIMIT 1;

UPDATE Result A INNER JOIN temp_table_result B ON(A.type = B.Type) 
  SET  A.RecDay=B.RecDay, A.NextRecDay=B.NextRecDay, A.MinHours=B.MinAmmount;

DROP TEMPORARY TABLE IF EXISTS temp_table;
DROP TEMPORARY TABLE IF EXISTS temp_table_result;

END;
$$
DELIMITER ;

edited Mar 31, 2017 at 9:03

answered Mar 31, 2017 at 8:57

Sagar Gangwal

8,1303 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Bullfrog Over a year ago

That worked, your answer is so prompt I can't except it straight away. I am going to have to go through it to find what you changed. Thanks

Bullfrog Over a year ago

One comma; I spent 6 hours trying to find.

Sagar Gangwal Over a year ago

@Bullfrog you will definitely get it by experience Only..!! And You will do it.

Rick James Over a year ago

This is a rare case -- the "near" in 1064 was off by one token. Why? It treated pType as the alias for the previous line, then got confused by as ....

Collectives™ on Stack Overflow

MySql MariaDB create Procedure error

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related