Pages

Tuesday, July 19, 2016

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.

1st 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;  
                                                                                            



2nd 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)


3rd 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)


4th 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:

No comments:

Post a Comment