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.