RE: SQL Select Statement in a Stored Procedure Question - Got Futher - But....

Ok, we've come this far - 

I have created a UDF called CWMPINH.TESTUDF which takes two input params
in_company and in_itemnum.  The function calls an existing stored procedure
"CWMPINH.GETITEMCOPY" which takes the same two input parms.  GETITEMCOPY
returns a string as an output param, not as a resultset.

Create Function cwmpinh.testudf( in_company character( 3 ), in_itemnum
varchar( 14 ) ) Returns VarChar( 2500 ) Language SQL Deterministic
Reads    SQL DATA
Returns NULL on NULL Input
DisAllow PARALLEL
Return ( 'call cwmpinh.getitemcopy(in_company, in_itemnum )' ) ;


I tried created a function to call the external RPGLE program GETITEMCOPY
calls, but had no luck.

So my SQL statement looks like:
SELECT A.ASCOMP AS COMPANY,
TRIM ( A.ASBECD ) AS ITEM,
CWMPINH.TESTUDF( A.ASCOMP, A.ASBECD ) AS ITEMDESCRIP 
FROM CWMPDTA.INIOFRL1 A 
WHERE A.ASCOMP = '001'

However, I get the following error:
Message: [SQL0204] TESTUDF in CWMPINH type *N not found. Cause . . . . . :
TESTUDF in CWMPINH type *N was not found. If this is an ALTER TABLE
statement and the type is *N, a constraint or partition was not found. If
this is not an ALTER TABLE statement and the type is *N, a function,
procedure, or trigger was not found. If a function was not found, TESTUDF is
the service program that contains the function.  The function will not be
found unless the external name and usage name do not match exactly.  Examine
the job log for a message that gives more details on which function name is
being searched for and the name that did not match.

In Operations Nav I can see that CWMPINH.TESTUDF does exist.  I'm not sure
where to go from here.

Deb


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of DebbieKelemen
Sent: Thursday, March 23, 2006 8:55 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL Select Statement in a Stored Procedure Question

Thanks, Rob.

Deb

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Thursday, March 23, 2006 6:10 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL Select Statement in a Stored Procedure Question

Perhaps you don't want a store procedure.  Instead, you want a UDF or User 
Defined Function
Then his select statement would look like,
select itemnum as ItemNum,
MyUDF(itemnum) as ItemDescription
from db2table
I've even written UDF's in RPG, but they could be done using straight SQL.
http://faq.midrange.com/data/cache/185.html

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstcfsc
htm#cfsc

Rob Berendt