What is the error in my mysql user defined function?

Question

Syntax error at line 6 in mysql 8.0, The function created as below

CREATE FUNCTION getStokMiktar(p_sto_id INT, p_dep_id INT)
RETURNS DOUBLE
BEGIN
DECLARE ret DOUBLE

SET ret = (SELECT SUM(CASE WHEN sth_depoid_cikis = p_dep_id THEN -sth_miktar
                           WHEN sth_depoid_giris = p_dep_id THEN sth_miktar
                            ELSE 0
                            END CASE)
            FROM stokhareketleri
            WHERE 
                (sth_depoid_cikis = p_dep_id OR sth_depoid_giris = p_dep_id)
                AND sth_stokid = p_sto_id)

RETURN ret
END

And my table's structure is :

CREATE TABLE `stokhareketleri` (
     `sth_id` BIGINT(20) NOT NULL AUTO_INCREMENT,
     `sth_stokid` INT(11) NOT NULL,
     `sth_giriscikis` TINYINT(4) NOT NULL,
     `sth_evraktipi` INT(11) NOT NULL,
     `sth_depoid_cikis` INT(11) NOT NULL,
     `sth_depoid_giris` INT(11) NOT NULL,
     `sth_miktar` DOUBLE NOT NULL,
     PRIMARY KEY (`sth_id`)
    )
    COLLATE='utf8_turkish_ci'
    ENGINE=InnoDB
    ;

What is the error at line 6? Thanks in advance.

None of your statements are terminated, there is no end case in mysql simply end and -sth_miktar should be enclosed in quotes AND I see no evidence that you have set delimiters, — P.Salmon
– P.Salmon, Commented Aug 4, 2019 at 6:53

James · Accepted Answer · 2019-08-04 08:21:03Z

1

As mentioned by @Salmon, try this

delimiter $$
CREATE FUNCTION getStokMiktar(p_sto_id INT, p_dep_id INT)
RETURNS DOUBLE
BEGIN
DECLARE ret DOUBLE;

SELECT 
SUM(CASE    WHEN sth_depoid_cikis = p_dep_id THEN - sth_miktar 
            WHEN sth_depoid_giris = p_dep_id THEN sth_miktar 
            ELSE 0 END) INTO ret
FROM stokhareketleri
WHERE p_dep_id IN (sth_depoid_cikis,sth_depoid_giris)
AND sth_stokid = p_sto_id;

RETURN ret;
END $$

answered Aug 4, 2019 at 8:21

James

1,8292 gold badges11 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

What is the error in my mysql user defined function?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related