Re: Creating a SQL UDF

Buck,

I'm pretty sure that SQL will automatically promote from CHAR to VARCHAR. However, it does not do the opposite (VARCHAR to CHAR)

so your exact scenario of passing the first param as a char would not be a problem. However, Robert's second parameter is a CHAR, so this would indeed cause a problem if you passed a VARCHAR to it.

And when you type a literal (instead of providing an actual database column) character literals are presumed to be VARCHAR.

So, that's the likely problem... Robert is passing a VARCHAR for the 2nd parameter... so due to overloading the system can't find the function. That would be my guess.





On 9/26/2013 2:02 PM, Buck Calabro wrote:

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