Re: Create UDF for SQL function

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

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.  
>   
>                 
>