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 - 1947 days ago
Last viewed on: 13 Nov 2019 (4959 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


(Sign in to Post a Comment)
Posted by: neilrh
Premium member *
Jackson, MI
Comment on: SQL Function with parameters
Posted: 8 years 25 days 58 minutes ago

What date format did you use to create the function.  I notice here that my default is *MDY, which in RPG/SQL I override to *ISO.  The RPG procedure will be expecting a full 4 digit year date, if your SQL is working in 2 digit years there may be a problem.  I have not worked much with User Defined Functions, so this is a guess on my part, but from prior experience with SQL is where I would start looking.

You might also check the joblog and see if anything is reporting a problem.

Posted by: neilrh
Premium member *
Jackson, MI
Comment on: SQL Function with parameters
Posted: 8 years 25 days 57 minutes ago

Or it could be that Embarassed

btw you misspelt endDate in your create function.

Posted by: Ringer
Premium member *
Comment on: SQL Function with parameters
Posted: 8 years 21 days 3 hours 30 minutes ago

I like the way you are allowing the *SRVPGM to be found in the *LIBL instead of hardcoding the libary name.  Most people hardcode it which makes testing in multiple environments quite difficult.

Chris Ringer

Posted by: bobcozzi
Site Admin ****
Comment on: SQL Function with parameters
Posted: 8 years 16 days 4 hours 3 minutes ago

Ringer, I agree; hard coding library names is problematic-particularly for development/testing. The system I'm working with now, had hard-coded names all over the place and in some cases, the library names are wrong!