SQL User Defined Functions in iSeries(AS400)

SQL comes with a lot of standard functions such as finding the maximum, minimum or distinct values for a column within a database table. But there comes a time when you wish that if you had that special function that will make your programming logic a piece of cake then you must take matters into your own hand and create your own SQL function. In this tutorial we are going to learn how to create custom functions using both SQL and RPGLE as the programming language. Lets create a simple order table so that we can use that as a template for our examples below.

//Create the library from the command line
CRTLIB LIB(SQLDEMO)  
ADDLIBLE LIB(SQLDEMO) 

//Start SQL Interactive Session (STRSQL)
CREATE TABLE SQLDEMO/OTABLE (               
  O1ITEM CHAR (10 ) NOT NULL,               
  O1DESC CHAR (50 ) NOT NULL WITH DEFAULT,  
  O1QTY DEC (9,0) NOT NULL WITH DEFAULT,    
  O1PRICE DEC (11,2) NOT NULL WITH DEFAULT, 
  O1DISC DEC (5,2) NOT NULL WITH DEFAULT)   

//Insert some data into the Order Table
INSERT INTO SQLDEMO/OTABLE VALUES('A001', 'A Item Description', 1, 11.11, 10.0)     
INSERT INTO SQLDEMO/OTABLE VALUES('A002', 'B Item Description', 2, 22.22, 0.0)
INSERT INTO SQLDEMO/OTABLE VALUES('C002', 'C Item Description', 3, 33.33, 15.0)   

//Query the table to see your data
SELECT * FROM SQLDEMO/OTABLE  
O1ITEM      O1DESC                                                    O1QTY          O1PRICE   O1DISC
A001        A Item Description                                            1            11.11    10.00
A002        B Item Description                                            2            22.22      .00
C002        C Item Description                                            3            33.33    15.00                                        

Now let's create a function that can give us the item detail price extension basically taking the discount off the price and then multiply that by quantity ordered. The function needs 3 inputs quantity, price and discount and its going to return the extended price.

//Extended Price User define SQL function 
Create Function SQLDEMO/EXTPRICE(                                
  qty dec(9,0),                                                  
  price dec(11,2),                                               
  discount dec(5,2))                                             
returns dec(11,2)                                                
language SQL                                                     
BEGIN                                                            
  return( qty * (price * cast((1 - discount/100) as dec(11,2))));
END                                                              

//Our new Query
Select O1ITEM, O1DESC,                    
EXTPRICE(O1QTY,O1PRICE,O1DISC) from OTABLE

O1ITEM      O1DESC                                                    EXTPRICE
A001        A Item Description                                            9.99
A002        B Item Description                                           44.44
C002        C Item Description                                           84.99                                          

Well SQL is very powerful and you can create a lot of custom function like above but what if you are more comfortable writing them in RPGLE or you already have programs that do more complex logic and you would like to leverage the existing code base. In the next example we are going to recreate the exact same function but using RPGLE as the programming language.

RPGLE source to create Extended price subprocedure

 *Service Program for User Defined SQL functions                        
h option(*nodebugio)                                                    
                                                                        
d extPrice        PR            11p 2 ExtProc('EXTPRICE')               
d qty                            9p 0 const                             
d price                         11p 2 const                             
d discount                       5p 2 const                             
                                                                        
c                   move      *on           *inlr                       
c                   return                                              
                                                                        
P extPrice        B                   export                            
d extPrice        PI            11p 2                                   
d qty                            9p 0 const                             
d price                         11p 2 const                             
d discount                       5p 2 const                             
                                                                        
d $extPrice       s             11p 2                          
                                                               
 /free                                                         
                                                               
   //you can do complex routines here such as reading files,   
   //calling other programs, etc.                              
                                                               
   $extPrice = qty * (price * (1 - discount * .01));           
   return $extPrice;                                           
                                                               
 /end-free                                                     
                                                               
P                 E              

Next we have to create the Module and then the Service program. Also before we recreate our SQL function again delete the previous one that we created.

CRTRPGMOD MODULE(SQLDEMO/MYSERVICE) SRCFILE(SQLDEMO/SRC) SRCMBR(MYSERVICE)     

CRTSRVPGM SRVPGM(SQLDEMO/MYSERVICE) MODULE(SQLDEMO/MYSERVICE) EXPORT(*ALL)

//Start SQL Interactive Session (STRSQL)
Drop function EXTPRICE  

Create the User Define SQL function

Create Function SQLDEMO/EXTPRICE(           
  qty dec(9,0),                             
  price dec(11,2),                          
  discount dec(5,2))                        
returns dec(11,2)                           
language RPGLE                              
deterministic                               
no SQL                                      
external name 'SQLDEMO/MYSERVICE(EXTPRICE)' 
parameter style GENERAL                     
program type SUB      

Run the exact same query as we did before and check the results.

Select O1ITEM, O1DESC,                       
EXTPRICE(O1QTY,O1PRICE,O1DISC) from OTABLE