Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
SQL Function with parameters
has no ratings.
Published: 20 Oct 2011
Revised: 16 Jul 2014 - 1107 days ago
Last viewed on: 27 Jul 2017 (4106 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 
      /free                                      
           if (eDate > bDate);                   
              return %DIFF(eDate:bDate: *MONTHS);
           else;                                 
              return %DIFF(bDate:eDate: *MONTHS);
           endif;                                
                                                 
      /end-free                                  
     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;                             
    CREATE FUNCTION MONTHDIFF(strDate DATE, endDate DATE)
          RETURNS INT                                    
          LANGUAGE RPGLE                                 
          EXTERNAL NAME 'SQLTOOLS(MONTHDIFF)'            
          DETERMINISTIC                                  
          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

COMMENTS

(Sign in to Post a Comment)
Posted by: neilrh
Premium member *
Jackson, MI
Comment on: SQL Function with parameters
Posted: 5 years 9 months 8 days 5 hours 22 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: 5 years 9 months 8 days 5 hours 21 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: 5 years 9 months 4 days 7 hours 53 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 ****
Chicagoland
Comment on: SQL Function with parameters
Posted: 5 years 8 months 29 days 8 hours 26 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!