phpmyadmin Question posted in
The official documentation can be found here.

Create function returns double value – Phpmyadmin

I want to make some function in phpmyadmin

DELIMITER //
CREATE FUNCTION total_bayar (order_id char(10)) 
RETURNS double
DETERMINISTIC
BEGIN 
  DECLARE dist double;
  SET dist = SELECT sum(qty * price) FROM detail_masakan WHERE order_id = order_id;
  RETURN dist;
END//
DELIMITER ;

Query SQL: Dokumentasi

CREATE FUNCTION total_bayar (order_id char(10)) 
RETURNS double
DETERMINISTIC
BEGIN 
  DECLARE dist double;
  SET dist = SELECT sum(qty * price) FROM detail_masakan WHERE order_id = order_id;
  RETURN dist;

END

MySQL says: Dokumentasi

#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 ‘SELECT sum(qty * price) FROM detail_masakan WHERE order_id = order_id;
RETURN’ at line 6

Tags:

2

Answers


  1. 0

    Appears it didn’t like your use of the SET command. Use SELECT…INTO instead.

    DELIMITER // 
CREATE FUNCTION total_bayar (order_id char(10)) RETURNS double 
DETERMINISTIC 
BEGIN 
    DECLARE dist double; 

    SELECT sum(qty * price) INTO dist FROM detail_masakan WHERE order_id = order_id; 
    RETURN dist; 
END// DELIMITER ;
    Login or Signup to reply.
  2. 0

    You need parentheses for the subquery:

    SET dist = (SELECT sum(qty * price) FROM detail_masakan WHERE order_id = order_id);

    However, this still won’t do what you want, because the variable names are messed up — and will get confused with the column names.

    I would suggest:

    DELIMITER //

CREATE FUNCTION total_bayar (in_order_id char(10)) 
RETURNS double
DETERMINISTIC
BEGIN 
  DECLARE out_dist double;

  SELECT out_dist := sum(dm.qty * dm.price)
  FROM detail_masakan dm
  WHERE dm.order_id = in_order_id;

  RETURN out_dist;
END//
DELIMITER ;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search