Re: Creating a SQL UDF

On 9/26/2013 2:26 PM, Robert Mullis wrote:

I have a service program (SYSRVUTL) with the following procedure in it:



D Remove_Non_AlphaNumeric...
D pr 65535a varying opdesc
D inData 65535a const varying
D inCompress n const



I want to create a UDF to use this procedure in embedded SQL, but so far
I have been unsuccessful. I have tried numerous times creating the
function with slight variations and each time it appears to create. But
when I try and run it in an SQL Select, it comes back and tells me the
function is not found in *LIBL. I now it was created in a library that
is in my library list.



This was my last attempt at creating:



CREATE FUNCTION WORKLB/REMOVE_NON_ALPHANUMERIC
(INDATA VARCHAR(32740), COMPRESS CHAR(1))
RETURNS VARCHAR(32740)
LANGUAGE RPGLE
DETERMINISTIC
NO SQL
RETURNS NULL ON NULL INPUT
EXTERNAL NAME 'WORKLB/SYSRVUTL(REMOVE_NON_ALPHANUMERIC)'
PARAMETER STYLE GENERAL


Anyone have any suggestions? I am sure it is simple, but this is my
first attempt at creating a UDF.

So SQL allows something called overloading. One can create a function
named GetName that takes a numeric customer number and another GetName
in the same schema that takes a character item number and SQL tells them
apart by the parameter types the caller supplies.

I'm looking at the VARCHAR() parameter type and wondering if your caller
is supplying a VARCHAR() or a CHAR() type. They are different, so if
the caller were supplying a CHAR() the parameter signatures would not
match and therefore the REMOVE_NON_ALPHAMERIC(CHAR()) really wouldn't be
found in *LIBL.
--buck