Function in mysql (error 1064)

Question

Request

CREATE FUNCTION AverageRateofProduct
  (  p_toDate datetime,  p_productBatchId longtext  ) 
  RETURNS decimal(18,2)  
  BEGIN  

    Declare p_averageRate decimal(18,2)  ;

    if((SELECT IFNULL(sum(inwardQuantity),0) FROM tbl_StockPosting WHERE  (date < p_toDate and  productBatchId =p_productBatchId))>0)  
        then    
        set p_averageRate = 
                            select IFNULL(sum((inwardQuantity * rate)/sum(inwardQuantity)),0)  
                            from tbl_StockPosting where (date < p_toDate and  productBatchId =p_productBatchId) ;
    END if;

    return IFNULL(p_averageRate,0)  ;
 end

gives error 1064 on line 11.

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'selec
t IFNULL (sum ((inwardQuantity * rate) / sum (inwardQuantity)), 0)
                                                        fro 'at line 11

Answer 1

change the condition i think with

if(SELECT IFNULL(sum(inwardQuantity),0) FROM tbl_StockPosting 
   WHERE (date < p_toDate and productBatchId = p_productBatchId)) 

Answer 2

Try this one

 SELECT IF(inwardQuantity IS NULL,0,sum(inwardQuantity * rate) /sum(inwardQuantity))
    from tbl_StockPosting where (date < p_toDate and  productBatchId =p_productBatchId) ;

Answer 3

Try the following:

CREATE FUNCTION AverageRateofProduct (p_toDate DATETIME, p_productBatchId LONGTEXT) RETURNS DECIMAL(18,2)  
    READS SQL DATA
BEGIN  
    DECLARE p_averageRate DECIMAL(18,2);

    IF ((SELECT IFNULL(SUM(inwardQuantity),0) FROM tbl_StockPosting sp 
         WHERE sp.date < p_toDate AND productBatchId = p_productBatchId) > 0) THEN
        SELECT IFNULL((SUM(inwardQuantity * rate)/SUM(inwardQuantity)),0) INTO p_averageRate
        FROM tbl_StockPosting sp WHERE sp.date < p_toDate AND productBatchId = p_productBatchId;
    END IF;

    RETURN IFNULL(p_averageRate,0);
END