function in mysql (1064 error)

Question

The Query

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 and error 1064 in 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

function in mysql (1064 error)

3 Answers