MYSQL SELECT with custom Function

Question

I want to be able to select records using custom function in mysql.

I tried the following code but I got null result where there is data:

SELECT id, getAmountS(id) FROM product WHERE id=2

DELIMITER $$
CREATE FUNCTION getAmountS(pid INT(11)) RETURNS DOUBLE(12, 2) DETERMINISTIC BEGIN
    DECLARE sellvalue DOUBLE(12, 2) ;
    SELECT
        SUM( CASE WHEN s.invoicestatus = 'Active' THEN ROUND(
        (r.rate * 1 -((r.rate * 1) * r.discount / 100))+((
        r.rate * 1 -( (r.rate * 1) * r.discount / 100)) * r.gstrate / 100),
                2  ) ELSE 0 END) INTO sellvalue FROM  morder r
        LEFT JOIN sale s ON  r.saleid = s.id
        WHERE r.productid = pid;
    RETURN sellvalue;
END $$

Please help.

Table: product

id(INT), productname(VARCHAR), manufacturer(INT)

Table: morder

id(INT), saleid(INT), gstrate(INT), rate(DOUBLE), productid(INT), discount(DOUBLE)

Table: sale

id(INT), invoiceno(VARCHAR), invoicestatus(ENUM), invoiceDate(DATE)

The following statement works fine:

SELECT SUM( CASE WHEN s.invoicestatus = 'Active' THEN ROUND(
        (r.rate * 1 -((r.rate * 1) * r.discount / 100))+((
        r.rate * 1 -( (r.rate * 1) * r.discount / 100)) * r.gstrate / 100), 2  ) ELSE 0 END) sellvalue FROM  morder r
        LEFT JOIN sale s ON  r.saleid = s.id
        WHERE r.productid = 30340

If you run the select statement on it's own do you get a result? — P.Salmon
– P.Salmon, Commented Sep 3, 2018 at 13:26
How come your where have s.invoicestatus = 'Approved' but your sum have s.invoicestatus = 'Active' the result should be 0 not null. so your query probably dont return any row — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Sep 3, 2018 at 13:31
@P.Salmon, I got result with the select statement. s.invoicestatus is my mistake. But that does not solved the problem either. — Aman from Silchar
– Aman from Silchar, Commented Sep 3, 2018 at 13:37
@fancyPants, thanks. but how? I hope you have a better solution — Aman from Silchar
– Aman from Silchar, Commented Sep 3, 2018 at 13:40

Juan Carlos Oropeza · Accepted Answer · 2018-09-03 13:46:33Z

1

You need test your code from basic to complex. Add one thing each time until you found the problem.

Starting with this basic function. I can return the value so the function structure is ok. the problem is the query

SQL DEMO

CREATE FUNCTION getAmountS(pid INT(11)) RETURNS DOUBLE(12, 2) DETERMINISTIC BEGIN
    DECLARE sellvalue DOUBLE(12, 2) ;
    SELECT 2*pid INTO sellvalue FROM  Table1
    WHERE id = pid;
    RETURN sellvalue;
END;

If you add your own data to the rextexter demo we can dig deeper and see where is the problem.

answered Sep 3, 2018 at 13:46

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

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

4 Comments

Aman from Silchar Over a year ago

Thanks for the info. I have many data. some data return null, I got it right. Thanks for the help.

Juan Carlos Oropeza Over a year ago

As I thought the problem is the data and not the custom function? Then is this question solve?

Aman from Silchar Over a year ago

Why is null return instead of zero when there is no data?

Juan Carlos Oropeza Over a year ago

Because SUM (NULL) is NULL

Collectives™ on Stack Overflow

MYSQL SELECT with custom Function

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related