Create Stored Function in phpmyadmin 1

[TheLazyPig]

Hi!

I tried to create a new function in phpmyadmin that I copied the structures from other functions in the database but I get this error.

CREATE DEFINER =`root`@`%` FUNCTION `fnGetGrpLoanFaceAmt`(`polno` VARCHAR(20),`poc_no` INT) RETURNS decimal(11,2)
BEGIN
   DECLARE ben_value DECIMAL(11,2);

   SET ben_value = (SELECT IF(SUM(ROUND(ben.VALUE,2)) IS NULL, 0, SUM(ROUND(ben.VALUE,2))) 
    	  	      FROM `group`.master_policy mp 
    	  	      LEFT JOIN `group`.policy_certificates pc ON pc.master_policy_id = mp.ID 
    	  	      LEFT JOIN `group`.loan_multi loan ON loan.master_policy_id = mp.id AND loan.certificate_no = pc.certificate_no 
    	  	      LEFT JOIN `group`.benefits ben ON ben.master_policy_id = mp.id AND ben.benefit_id = loan.benefits_id 
    	  	      LEFT JOIN `group`.premium_breakdown pb ON pb.master_policy_id = mp.ID AND pb.breakdown_id = loan.prem_breakdown_id 
    	  	     WHERE 1 = 1 
		       AND mp.policy_no = polno
		       AND pc.poc_no = poc_no
		       AND (loan.loan_status = 5 OR loan.loan_status = 'I' OR (pc.status_date BETWEEN ben.effective_date AND ben.termination_date)) 
                     ORDER BY ben.termination_date DESC
		   );
	  
   RETURN ben_value;
END

Thanks in advance!

 

[feherke]

Hi

I never used phpMyAdmin in practice, so maybe does not apply there, but when defining stored function through the MySQL command line client, you have to set [tt]delimiter[/tt] to something else. So I would try it like this ( actually tried and works in the command line client ) :

[highlight][b]delimiter[/b] $[/highlight][small]
[b]CREATE[/b] DEFINER [teal]=[/teal][i][green]`root`[/green][/i]@[i][green]`%`[/green][/i] [b]FUNCTION[/b] [i][green]`fnGetGrpLoanFaceAmt`[/green][/i][teal]([/teal][i][green]`polno`[/green][/i] [maroon]VARCHAR[/maroon][teal]([/teal][purple]20[/purple][teal]),[/teal][i][green]`poc_no`[/green][/i] [maroon]INT[/maroon][teal])[/teal] [b]RETURNS[/b] [maroon]decimal[/maroon][teal]([/teal][purple]11[/purple][teal],[/teal][purple]2[/purple][teal])[/teal]
[b]BEGIN[/b]
   [b]DECLARE[/b] ben_value [maroon]DECIMAL[/maroon][teal]([/teal][purple]11[/purple][teal],[/teal][purple]2[/purple][teal]);[/teal]

   [b]SET[/b] ben_value [teal]= ([/teal][b]SELECT IF[/b][teal]([/teal]SUM[teal]([/teal]ROUND[teal]([/teal]ben[teal].[/teal]VALUE[teal],[/teal][purple]2[/purple][teal]))[/teal] [b]IS NULL[/b][teal],[/teal] [purple]0[/purple][teal],[/teal] SUM[teal]([/teal]ROUND[teal]([/teal]ben[teal].[/teal]VALUE[teal],[/teal][purple]2[/purple][teal])))[/teal] 
              [b]FROM[/b] [i][green]`group`[/green][/i][teal].[/teal]master_policy mp 
              [b]LEFT JOIN[/b] [i][green]`group`[/green][/i][teal].[/teal]policy_certificates pc [b]ON[/b] pc[teal].[/teal]master_policy_id [teal]=[/teal] mp[teal].[/teal]ID 
              [b]LEFT JOIN[/b] [i][green]`group`[/green][/i][teal].[/teal]loan_multi loan [b]ON[/b] loan[teal].[/teal]master_policy_id [teal]=[/teal] mp[teal].[/teal]id [b]AND[/b] loan[teal].[/teal]certificate_no [teal]=[/teal] pc[teal].[/teal]certificate_no 
              [b]LEFT JOIN[/b] [i][green]`group`[/green][/i][teal].[/teal]benefits ben [b]ON[/b] ben[teal].[/teal]master_policy_id [teal]=[/teal] mp[teal].[/teal]id [b]AND[/b] ben[teal].[/teal]benefit_id [teal]=[/teal] loan[teal].[/teal]benefits_id 
              [b]LEFT JOIN[/b] [i][green]`group`[/green][/i][teal].[/teal]premium_breakdown pb [b]ON[/b] pb[teal].[/teal]master_policy_id [teal]=[/teal] mp[teal].[/teal]ID [b]AND[/b] pb[teal].[/teal]breakdown_id [teal]=[/teal] loan[teal].[/teal]prem_breakdown_id 
             [b]WHERE[/b] [purple]1[/purple] [teal]=[/teal] [purple]1[/purple] 
         [b]AND[/b] mp[teal].[/teal]policy_no [teal]=[/teal] polno
         [b]AND[/b] pc[teal].[/teal]poc_no [teal]=[/teal] poc_no
         [b]AND[/b] [teal]([/teal]loan[teal].[/teal]loan_status [teal]=[/teal] [purple]5[/purple] [b]OR[/b] loan[teal].[/teal]loan_status [teal]=[/teal] [i][green]'I'[/green][/i] [b]OR[/b] [teal]([/teal]pc[teal].[/teal]status_date [b]BETWEEN[/b] ben[teal].[/teal]effective_date [b]AND[/b] ben[teal].[/teal]termination_date[teal]))[/teal] 
                     [b]ORDER BY[/b] ben[teal].[/teal]termination_date [b]DESC[/b]
     [teal]);[/teal]

   [b]RETURN[/b] ben_value[teal];[/teal]
[b]END[/b][/small][highlight]$[/highlight]
[highlight][b]delimiter[/b] ;[/highlight]

( See MySQL Reference Manual | Stored Objects | Defining Stored Programs for somehow detailed explanation. )


Feherke.
feherke.github.io

 

[TheLazyPig]

Thanks for the reply I already fixed the error.

I also tried to add a delimiter but I still got an error.

So I found a post who has the same error and he/she used this code below and it worked for me.

/*!50003 DROP FUNCTION IF EXISTS `fnGetGrpEffDate` */;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE*/ /*!50003 FUNCTION `fnGetGrpEffDate`(polno VARCHAR(20), poc_no INT) RETURNS date
begin
  DECLARE effDate DATE;

  SET effDate = (SELECT IF(MIN(ben.effective_date) IS NULL,pc.current_effective_date,MIN(ben.effective_date))
		  		   FROM `group`.master_policy mp 
		  		   LEFT JOIN `group`.policy_certificates pc ON pc.master_policy_id = mp.ID 
		  		   LEFT JOIN `group`.loan_multi loan ON loan.master_policy_id = mp.id AND loan.certificate_no = pc.certificate_no 
		  		   LEFT JOIN `group`.benefits ben ON ben.master_policy_id = mp.id AND ben.benefit_id = loan.benefits_id 
		  		   LEFT JOIN `group`.premium_breakdown pb ON pb.master_policy_id = mp.ID AND pb.breakdown_id = loan.prem_breakdown_id 
		  		  WHERE 1 = 1 
		  		    AND mp.policy_no = polno
		  		    AND pc.poc_no = poc_no
		  		    AND (loan.loan_status = 5 OR loan.loan_status = 'I' OR (pc.status_date BETWEEN ben.effective_date AND ben.termination_date)) 
		  		  ORDER BY ben.termination_date DESC
				);
	RETURN effDate;
end */;;