RE: Creating an SQL function

Looks like your code is not returning a valid DECIMAL(9,0) value, so you'll
need to debug it. Here are some approaches you might take for debug:

1) run:
CREATE TABLE tempTable AS
(SELECT SUFFIX, SKUNO, AVAILQTY(SUFFIX, SKUNO)
FROM ITEMMSTR
WHERE SUFFIX = '0011' AND SKUNO = '000070')
WITH DATA

And then peruse the data you have in the tempTable to verify what is it
your UDF is returning. That may give you a hint as to what's going on.

2) I often use iSeries Navigator System Debugger to debug UDFs. You can get
to it through menu options in the Run Sql Scripts window. This debugger
lets you debug in SQL or native code.
3) you can use traditional green-screen debugger, but you'll need two green
screen sessions. In session A do a STRSRVJOB on session B's job, followed
by STRDBG on your service program's procedure and then set a break point.
In session B enter interactive SQL and run the SELECT statement that hits
your UDF.

HTH, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Creating an SQL function

Good morning all
I am trying to create an SQL function that uses a sub procedure within a
service program
Here is what I attempted
Within STRSQL

create function MYLIB/AVAILQTY (CHAR, CHAR)
returns DECIMAL(9,0)
External Name 'PRODLIB/SERVPGM(SUBPROC)'
Language RPGLE
Disallow Parallel
No SQL
Parameter Style DB2SQL
Deterministic

When I attempt to use this, for example
SELECT SUFFIX, SKUNO, AVAILQTY(SUFFIX, SKUNO)
FROM ITEMMSTR WHERE SUFFIX = '0011' AND SKUNO = '000070'

What I get is
ITEM SKUNO AVAILQTY
NO.
SUFFIX
0011 000070 ++++++++++++
The input to the sub procedure is 2 fields 4 characters, 6 characters
The output from the sub procedure is decimal(9,0) as noted above
I'll be honest
I don't know where to start on debugging this
If anyone can help, it would be much appreciated)


Thanks in advance



Alan Shore