# 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
```

#### 11 comments :

1. Married life has been mediocre fifa 14 coins since the light, sir busy cheap fifa 14 coins day from work to go to work, in order to take care of the crystallization of our love, I gave up their jobs and became full-time fifa 14 coins housewives.

2. Hi.... I tried the sample code exactly the same.. precisely i did a CCP.. but when I tried to invoke the function in STRSQL I get the below error.
EXTPRICE in *LIBL type *N not found.
As I am a beginner please help me with this.

Thanks,

3. Android Tutorial Basic to Advanced: Click Here For Tutorial

4. Try Prefixing the EXTPRICE with the location of the object* followed by either a '.' or a '/' (depending on your setup).

In the example this was SQLDEMO. If your unsure try WRKOBJ *ALL/EXTPRICE from the command line (not STRSQL).

5. I like your point of choice for website yet need to recommend you for sharing some more data with respect to your subject so we can comprehend your idea all the more plainly.
Business Blog Updates

6. We are offering Website design or e-commerce website!!!

Android Application Software

7. Thanks for the great post Friv.Com, Friv.Com! I quite enjoyed reading it Kizi 6000, Kizi 7000 you can be a great author. Kizi 8000, Kizi 9000 I will remember to bookmark your blog and definitely will come back later Kizi 360, Kizi.Com. I want to encourage continue your great job Yepi 300, Yepi 5, have a nice day Yepi 600, Yepi 9000!

8. Thank you for your guidance so sweet Yepi.Com, Friv 9 all this time later in Friv 8, Friv 7, I have found it Friv 6, Friv 100 and loved the final result Friv 6000, Friv 7000. I appreciate time you spend sharing your skills on Friv 8000,Friv 9000, Yepi Games !