scheduler events in mysql using if conditions

Question

I have written the below query

  CREATE EVENT test_event_03
  ON SCHEDULE EVERY 1 MINUTE
  STARTS CURRENT_TIMESTAMP
  ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
  DO
  DECLARE cnt1 AS BIGINT
  SELECT COUNT(*) AS cnt1, d.Invoice_Date AS Invoice_Date1 FROM Depot_Sales__c AS d,  Advance_Payment_Request__c A, Supplier_Payment__c S WHERE d.Supplier_Code=A.Supplier AND d.Supplier_Code=S.Supplier AND S.Supplier=80
   IF cnt1=0 THEN
   SELECT COUNT(*) FROM Depot_Sales__c AS d 
   END IF;

I am getting the below error

 Error Code : 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 'declare cnt1 as bigint
  SELECT COUNT(*) as cnt1, d.Invoice_Date as Invoice_Date1 ' at line 8

  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  'IF cnt1=0 THEN' at line 10

Why am I getting this error?

Answer 1

The problem is that you can only use the DECLARE statement inside a BEGIN..END block, and only at the beginning of that block. See the MySQL documentation at http://dev.mysql.com/doc/refman/5.0/en/declare.html. You'll need to wrap your do statement in a BEGIN...END block. And I think you'll also need to change the delimiter so you can do more than one statement. So, it would end up being something like:

  delimiter |
  CREATE EVENT test_event_03
  ON SCHEDULE EVERY 1 MINUTE
  STARTS CURRENT_TIMESTAMP
  ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
  DO
  BEGIN
  DECLARE cnt1 AS BIGINT;
  SELECT COUNT(*) AS cnt1, d.Invoice_Date AS Invoice_Date1 FROM Depot_Sales__c AS d,  Advance_Payment_Request__c A, Supplier_Payment__c S WHERE d.Supplier_Code=A.Supplier AND d.Supplier_Code=S.Supplier AND S.Supplier=80;
   IF cnt1=0 THEN
   SELECT COUNT(*) FROM Depot_Sales__c AS d; 
   END IF;
 END |
 delimiter ;