Re: Creating an SQL function

Alan,

A shot in the dark, but should you not supply a length for the character
parms in the function? Thus:

create function MYLIB/AVAILQTY (CHAR(4), CHAR(6))

Perhaps now SQL takes default lengths for the parms on the call and the
procedure returns junk data, as it cannot retrieve the correct record/data
based on the passed vallues.

With regards,
Carel Teijgeler

*********** REPLY SEPARATOR ***********

On 29-9-2008 at 13:32 Alan Shore wrote:

Here you go Charles
D SUBPROC pr 9 0
D ItemSuffix 4 value
D Sku# 6 value

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)