Step by Step guide to create SQL User Defined Functions(UDF) - How to create a SQL Function using RPGLE

How to convert long Julian to YYYYMMDD format using SQL 

In the system that we have in our office, all the date values are in long Julian format. When we are working with SQL it is a headache to convert those long Julian dates to YYYYMMDD numeric format. So I have decided to create two SQL functions. One is for convert 7-character long Julian date into YYYYMMDD numeric value and other one is for convert 7-Digits long Julian date into YYYYMMDD numeric value. In my previous post I showed how to create a SQL scalar function, this time I am going to create SQL scalar function and external scalar function both. 

First will see how to achieve above task using an external scalar function.

1^st Step

Let’s create RPGLE module.  This is a NOMAIN module. In that module I have created two procedures. One procedure accepts a 7-char parameter and returns 8-digits value and the other one accepts a 7-digits parameter and returns 8-digits value.

*-------------------------------------------------------------------       ctl-opt nomain;                                               *-------------------------------------------------------------------                                                                           /////////////////////////////////////////////////////////////       // Prototype definitions                                   //       /////////////////////////////////////////////////////////////       //Prototype for julToNum                                            dcl-pr julToNum zoned(8);                                             *n zoned(7) const;                                                end-pr;                                                                                                                                 //Prototype for jchToNum                                            dcl-pr jchToNum zoned(8);                                             *n char(7) const;                                                 end-pr;           /////////////////////////////////////////////////////////////       // This Procedure converts numeric long julian date        //       // into ISO numeric date.                                  //       /////////////////////////////////////////////////////////////       dcl-proc julToNum export;                                            //Prototype interface for incoming parms                           dcl-pi *n zoned(8);                                                  julDat zoned(7) const;                                           end-pi;                                                                                                                               monitor;                                                             return %dec(%char(%date(julDat:*longjul):*iso0):8:0);            on-error;                                                            return 0;                                                        endmon;                                                           end-proc julToNum;                                                  /////////////////////////////////////////////////////////////        // This Procedure converts character long julian date      //        // into ISO numeric date.                                  //        /////////////////////////////////////////////////////////////        dcl-proc jchToNum export;                                              //Prototype interface for incoming parms                             dcl-pi *n zoned(8);                                                    julDat char(7) const;                                              end-pi;                                                                                                                                    monitor;                                                               return %dec(%char(%date(%int(julDat):*longjul):*iso0):8:0);         on-error;                                                               return 0;                                                          endmon;                                                            end-proc jchToNum;

2^nd Step

Compile RPGLE module.

To compile a RPGLE module you have to use CRTRPGMOD command.

In command line type below command and press enter. 

CRTRPGMOD MODULE(YOUR_LIB/YOUR_MOD) SRCFILE(YOUR_LIB/YOUR_SOURCE_FILE)

3^rd Step

Create a service program from the above created module.

In command line type below command and press enter. 

CRTSRVPGM SRVPGM(YOUR_LIB/YOUR_MOD) EXPORT(*ALL)

4^th Step

Create two SQL functions. One for 7-Char long Julian to 8-numeric and other one for 7-digits long Julian to 8-numeric.

Type STRSQL in your command line and create following two SQL functions. 

create or replace function YOUR_LIB/JULTONUM (inNumeric NUMERIC(7,0))                  returns NUMERIC(8,0)                      language rpgle                            parameter style general                   deterministic                             no sql                                     returns null on null input                no external action                        not fenced                                no final call                             allow parallel                            no scratchpad                              external name 'YOUR_LIB/YOUR_MOD(JULTONUM)'

create or replace function YOUR_LIB/JCHTONUM (inChar CHAR(7))                          returns NUMERIC(8,0)                      language rpgle                            parameter style general                    deterministic                             no sql                                    returns null on null input                no external action                        not fenced                                no final call                              allow parallel                            no scratchpad                             external name 'YOUR_LIB/YOUR_MOD(JCHTONUM)'

Let’s select using created functions…

SELECT numb_Jul, YOUR_LIB.jultonum(numb_Jul),        char_Jul, YOUR_LIB.jchtonum(char_Jul) FROM YOUR_LIB.YOUR_FILE

Numb_Jul column contains 7-digits long Julian values and char_Jul contains 7-char long Julian values.

when I execute the query.

Result table

NUMB_JUL	JULTONUM	CHAR_JUL	JCHTONUM
1,992,251	19,920,907	1992248	19,920,904
1,991,250	19,910,907	1991235	19,910,823
1,998,066	19,980,307	1993292	19,931,019
1,992,081	19,920,321	1992065	19,920,305
1,995,097	19,950,407	1993064	19,930,305

Even I used the external scalar function in above example, we can achieve the same objectives using SQL scalar functions as below.

CREATE or replace FUNCTION YOUR_LIB/JCHTONUM (                 inChar CHAR(7))                                            returns NUMERIC(8,0)                                      LANGUAGE SQL                                              NOT DETERMINISTIC                                         READS SQL DATA                                            CALLED ON NULL INPUT                                      DISALLOW PARALLEL                                         BEGIN                                                       DECLARE ReturnVal NUMERIC(8,0) NOT NULL DEFAULT 0;          Declare Exit Handler For SQLException                       Return 0;                                                   return dec(substr(digits(year(date((inChar)))),7,4 ) ||          substr(digits(month(date((inChar)))),9,2 )||              substr(digits(day(date((inChar)))),9,2 ));        end

CREATE or replace FUNCTION YOUR_LIB/JULTONUM   (                     inNumb NUMERIC(7,0))                                          returns NUMERIC(8,0)                                           LANGUAGE SQL                                                  NOT DETERMINISTIC                                             READS SQL DATA                                                CALLED ON NULL INPUT                                          DISALLOW PARALLEL                                             BEGIN                                                           DECLARE ReturnVal NUMERIC(8,0) NOT NULL DEFAULT 0;              Declare Exit Handler For SQLException                           Return 0;                                                       return dec(substr(digits(year(date(char(inNumb)))),7,4 ) ||          substr(digits(month(date(char(inNumb)))),9,2 )||              substr(digits(day(date(char(inNumb)))),9,2 ));        END

References:

• http://www.itjungle.com/fhg/fhg102214-story01.html 
• http://www.itjungle.com/fhg/fhg111214-story02.html
• https://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/db2/rbafzmstcreatef.htm