Cfrgtkky

I have been spending over 2 hours now trying to google my way to the answer.

I am completely new to SQL and MySQL and I have tried to write the following function:

CREATE FUNCTION fp_spinofffactor (id char(8), startdate date)

RETURNS float

BEGIN

DECLARE spinoffFactor float;               (ERROR- EXPECTED A ";")

select spinoffFactor = ISNULL(EXP(SUM(LOG(spinoffFactor))),1)

from( 

select case when (prev_price- divs) <= 0 THEN 1 

else (prev_price- divs)/prev_price end as spinoffFactor

    from (select

    divs,

    fp_v2.fp_prevUnadjPrice(id, ex_date) as prev_price

from (

select sum(fbd.p_divs_pd) as divs,fbd.p_divs_exdate as ex_date

from fp_v2.fp_basic_dividends fbd

where fbd.fsym_id = id

and fbd.p_divs_s_pd=1

and fbd.p_divs_exdate > startdate

group by fbd.p_divs_exdate ) a ) b ) c; 

return spinofffactor;              ERROR (Return is not valid at this position)

END                                ERROR (END IS NOT VALID AT THIS position)

But I get multiple syntax errors. I have written the errors where I get them.

I have a hard time finding information about the syntax rules of MySQL and the workbench.

Can anyone help ?

You need to provide delimiter in MySql workbench to tell where your code begins and ends.
Assuming your syntax is correct, you can write as below.

DELIMITER $$

CREATE FUNCTION fp_spinofffactor (id char(8), startdate date)

RETURNS float

BEGIN

DECLARE spinoffFactor float;       

    select spinoffFactor = ISNULL(EXP(SUM(LOG(spinoffFactor))),1)

    from( 

    select case when (prev_price- divs) <= 0 THEN 1 

    else (prev_price- divs)/prev_price end as spinoffFactor

        from (select

        divs,

        fp_v2.fp_prevUnadjPrice(id, ex_date) as prev_price

    from (

    select sum(fbd.p_divs_pd) as divs,fbd.p_divs_exdate as ex_date

    from fp_v2.fp_basic_dividends fbd

    where fbd.fsym_id = id

    and fbd.p_divs_s_pd=1

    and fbd.p_divs_exdate > startdate

    group by fbd.p_divs_exdate ) a ) b ) c; 

return spinofffactor;              

END$$

DELIMITER ;

You can also, run this from MySQL command prompt and it should work.