Midrange News for the IBM i Community

Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
SQL Function with parameters
has no ratings.
Published: 20 Oct 2011
Revised: 16 Jul 2014 - 1646 days ago
Last viewed on: 15 Jan 2019 (4743 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

SQL Function with parameters Published by: Bob Cozzi on 20 Oct 2011 view comments(4)

I have the following RPG Subprocedure that I want to turn into an SQL Function:


     P MONTHDIFF       B                   EXPORT
     D MONTHDIFF       PI            10I 0       
     D  bDate                          D   Const 
     D  eDate                          D   Const 
           if (eDate > bDate);                   
              return %DIFF(eDate:bDate: *MONTHS);
              return %DIFF(bDate:eDate: *MONTHS);
     P MONTHDIFF       E                         

 Once created I store this module into the SQLTOOLS service program and then run the following SQL statement to register it:


 -- Creates a function named MONTHDIFF that returns the  
 -- number of months between two dates.                  
    DROP FUNCTION MONTHDIFF;                             
          RETURNS INT                                    
          LANGUAGE RPGLE                                 
          NO SQL                                         
          NO EXTERNAL ACTION                             
          PARAMETER STYLE SQL                            
          DISALLOW PARALLEL;                             


Problem is, when I run it, I always get 0 returned.

What did a do wrong?

 [UPDATE 1: I got it to work. The PARAMETER STYLE SQL needs to be PARAMETER STYLE GENERAL once I changed it, it started working.

[UPDATE 2: IBM added a "native" SQL function to do this on IBM i v6r1. See this MidrangeNews Discussion Topic for details. But it does return the months as a decimal. Meaning the difference between May 1st and July 15 is 2.5 months. But I actually needed that.

Return to midrangenews.com home page.
Sort Ascend | Descend