CODEWITHSUNDEEP
mysqlfunction
Aman from Silchar
Aman from Silchar

Reputation: 13

MYSQL SELECT with custom Function

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

Upvotes: 0

Views: 4907

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

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.

Upvotes: 1

Related Questions