CODEWITHSUNDEEP
mysqlwhile-loopsyntax-error
sleepsleepsleep90731
sleepsleepsleep90731

Reputation: 959

Mysql looping error code #1064 inside stored procedure [revised]

I edited this question cause the answers below mentioned that when using while and begin, it should be inside a stored procedure.

So now I'm running this inside a stored procedure but I'm still getting an error.

counter;
while counter < 2
begin
GOTO counter

SELECT var1 AS LOCATION,
  ROUND(sum(CASE WHEN t2.tagid = var2 THEN t2.floatvalue ELSE 0 END), 2) AS MR
  ROUND(sum(CASE WHEN t2.tagid = var3 THEN t2.floatvalue ELSE 0 END), 2) AS Flw,
  ROUND(sum(CASE WHEN t2.tagid = var4 THEN t2.floatvalue ELSE 0 END), 2) AS Prs,
  ROUND(sum(CASE WHEN t2.tagid = var5 THEN t2.floatvalue ELSE 0 END), 2) AS Lvl
FROM table2 t2

INNER JOIN table1  t1
  ON t1.id = t2.tagid

WHERE t2.t_stamp = (SELECT MAX(t2.t_stamp) 
                    FROM table2  t2 
                    WHERE t1.id = t2.tagid)
counter = counter+1
end

1:
var1
var2
var3
var4
var5

2:
var1
var2
var3
var4
var5

This is the error I'm getting

MySQL said: #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 'begin GOTO counter SELECT var1 AS LOCATION, ROUND(sum(CASE WHEN t2.tagid' at line 2

And I didn't include the CREATE PROCEDURE cause I saw a post wherein it was easier to just go to the ROUTINES panel and paste the SQL Query without the CREATE, DELIMITER... etc. How should I fix this error?

Upvotes: 1

Views: 356

Answers (1)

peterm
peterm

Reputation: 92785

Control flow statements (WHILE, IF, etc) in MySQL can be used only in a context of a stored routine (procedure, function, trigger, event). You can't use them on their own in a SQL batch.

Flow Control Statements
MySQL supports the IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT constructs for flow control within stored programs.

Upvotes: 6

Related Questions