Smith Smith - 3 months ago 12
MySQL Question

FUNCTION "SUM does not exist"

I am using mysql 5.5.11, when i execute the script below

INSERT INTO payments(created, Amount, user, Remarks, orderid, paymethod)
VALUES('2016-09-03', 0.0, 'admin', '', 4, 'Cash');


I get error


SQL Error: FUNCTION mydb.SUM does not exist. Check the 'Function Name
Parsing and Resolution' section in the Reference Manual


This is the table schema

CREATE TABLE payments (
ID int AUTO_INCREMENT NOT NULL,
OrderID int,
Amount decimal(11,2),
Created varchar(20),
Remarks varchar(160),
user varchar(60),
PayMethod varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci,
/* Keys */
PRIMARY KEY (ID)
) ENGINE = InnoDB;


What could be the cause of the error

This is the trigger attached to the table

BEGIN
/* Trigger text */
UPDATE Orders Set Paid =(Select SUM (Amount) From Payments AS p Where p.OrderID = Orders.ID),PayMethod =new.PayMethod WHere Orders.id = new.OrderID;
UPDATE Orders Set Bal = Total - Paid WHere Orders.id = new.OrderID;
END

Answer

Look at trigger:

UPDATE Orders Set Paid =(Select SUM (Amount) From Payments AS p Where p.OrderID  = Orders.ID),PayMethod =new.PayMethod  WHere Orders.id = new.OrderID;

MySQL does not accept spaces between function name and parenthesis (unless you have set SQL_MODE=IGNORE_SPACE but that gives you other undesirable side effects)

You can check the SQL_MODE setting by running:

SHOW GLOBAL VARIABLES LIKE 'SQL_MODE';

MySQL documentation about this: link

Some example:

mysql> select sum(5);
+--------+
| sum(5) |
+--------+
|      5 |
+--------+
1 row in set (0.00 sec)

mysql> select sum (5);
ERROR 1305 (42000): FUNCTION sum does not exist

Solution: Remove space

UPDATE Orders Set Paid =(Select SUM(Amount) From Payments AS p Where p.OrderID  = Orders.ID),PayMethod =new.PayMethod  WHere Orders.id = new.OrderID;
Comments