SQL Function with parameters

Posted by: Bob Cozzi

Rogue Programmer

Cozzi Productions, Inc.

www.cozTools.com/qryfile

Chicagoland

SQL Function with parameters
has no ratings.

Published: 20 Oct 2011
Revised: 16 Jul 2014 - 3569 days ago
Last viewed on: 22 Apr 2024 (6045 views)

Main URL:

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.

Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)

Posted by: neilrh

Premium member *

Jackson, MI

Comment on: SQL Function with parameters
Posted: 12 years 6 months 4 days 23 hours 9 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: 12 years 6 months 4 days 23 hours 7 minutes ago

Or it could be that

btw you misspelt endDate in your create function.

Posted by: Ringer

Premium member *

Comment on: SQL Function with parameters
Posted: 12 years 6 months 1 days 1 hours 40 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

www.cozTools.com/qryfile

Comment on: SQL Function with parameters
Posted: 12 years 5 months 26 days 2 hours 13 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!