CODEWITHSUNDEEP
mysqlstored-functions
codeator
codeator

Reputation: 43

What is the error in my mysql user defined function?

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.

Upvotes: 0

Views: 51

Answers (1)

James
James

Reputation: 1829

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 $$

Upvotes: 1

Related Questions