Re: Create UDF for SQL function

Joe

The usual problem here is how you call the stored procedure - you have the input parameter defined as CHAR - if you use a literal character string in your call, SQL thinks it is a VARCHAR - and that does not match.

You have a couple choices -
1. Recreate the function with VARYING keyword on the parameter, then create the SP with varchar instead of char
2. In your CALL, cast the value as a CHAR - you can use CHAR() function to do that

HTH
Vern

joe.kozlowski@xxxxxxxxxxxxxxxxxxxx wrote:

Service program in specfic library not found on OpenCursor(). I created the module program FmtName and created a service program GETNAME in my library and hopefully I followed all of the rules create function, the following events took place: What could be possiblity wrong? Without the function the SQL completes but work for me in the program

Create Function FmtName (slsnnm char (30) ) returns char(30) language rpgle deterministic no sql external name 'JJKLIB/GETNAME(FmtName) ' parameter style general Function FMTNAME was created in JJKLIB. Session was saved and started again. **************************************************************************************
Display Service Program Information Service program . . . . . . . . . . . . : GETNAME Library . . . . . . . . . . . . . . . : JJKLIB Owner . . . . . . . . . . . . . . . . . : KOZLOWSJ Service program attribute . . . . . . . : RPGLE Detail . . . . . . . . . . . . . . . . . : *PROCEXP Procedure Exports: Procedure Name FMTNAME ********************MODULE***********************************************
D FmtName Pr 30a Varying D SlsName 30a Varying Const Options(*Varsize) *++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ * FmtName() Move First name first and last last when name = Smith, John *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++************ P FmtName B Export D FmtName Pi 30a Varying
This is the RPG Select statement in a specific program:

Select Wan8, FmtName(Cshnam), FmtName(Wslsnm), Cssf, Wpoint
>From FAA0075 a, FAA0078 b Where Cshnam=Wslsnm and Cssf<>' '
and WInvdt Between 81001 and 90617 Order By Cshnam

Open Cursor() the follow SQL error happens:

PREPARE of statement SQLSTATEMENT completed. Query options retrieved file QAQQINI in library QUSRSYS.
**** Starting optimizer debug message for query . User-defined function FMTNAME cannot be invoked. (1) GETNAME in JJKLIB type *SRVPGM not found. (2)
(1) Message . . . . : User-defined function FMTNAME cannot be invoked. Cause . . . . . : External program or service program GETNAME in library JJKLIB associated with user-defined function FMTNAME in library JJKLIB cannot be invoked for reason code 2. The reason codes and their meanings follow: 1 -- The program is not found. 2 -- Either the service program is not found or the program entry point FmtName does not exist. 3 -- The user is not authorized to the program or service program.
(2) Message . . . . : GETNAME in JJKLIB type *SRVPGM not found. Cause . . . . . : GETNAME in JJKLIB type *SRVPGM was not found. If the member name is *ALL, the table is not partitioned. 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, trigger or sequence object was not found. If a function was not found, GETNAME is the service program that contains the function. The function will not be found unless the external name and usage name 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.