I am responding again, in a separate message from my prior reply, to explain separately, an alternative means to resolve the original scenario; i.e. allowing a literal string as the argument for the function without having to use the CHAR cast function on the literal. While not as good IMO as having just one procedure using varying and just one UDF using VARCHAR to match, as I alluded in the prior message, but...

This resolution is possible without having to create a new procedure as well as allowing the original procedure to remain unchanged. This can be effected with overloading the UDF, just like with reference to a new procedure, but using an effective CAST to allow the originally coded procedure to process both SQL UDFs. For example, by making the new UDF function in the manner that is hopefully conveyed by the intended effect of the following pseudo-coding [an unavailable syntax which IMO would be much clearer than "sourcing" existing functions]:

create function isTollFree( ANI VarChar(20) CAST TO CHAR(20) ) ...

Referring to the original scenario which remains in the quoted message text below, the given procedure with a prototyped fixed-length 20-byte character string remains the same, and no additional procedure is created. The same UDF as shown and originally created would still be created using the CHAR(20) to match the expected input for the only argument of the UDF; i.e. matching declaration between the SQL and the prototyped fixed-length 20-byte character string. The next step is to create additionally, an overloaded UDF that will still be processed by that same external name [i.e. by the same procedure in the service program], but enabling the literal [which in the SQL docs the term is "constant"] value to be implicitly typed as VARCHAR by the SQL and thus enabling the SQL /function resolution/ feature to find a compatible function:

create function ccdlib/isTollFree /* same routine name */
( ANI varchar(20) ) /* enable character constant as VARCHAR */
returns char(1) /* same return data type */
specific istollfreeVC /* optional naming; e.g. enabling DROP */
source ccdlib/isTollFree(char(20)) /* "source" original UDF */

Having done the above additional CREATE FUNCTION, the request to perform the SQL isTollFree('800') for which the literal\constant value '800' is implicitly typed as VARCHAR, the compatible function named isTollFree with the matching VARCHAR declaration [specific named function isTollFreeVC] is located using Function Resolution and Best Fit processing by the SQL run-time [instead of failing with SQL0204 or SQL0440]. Having /sourced/ the original function defined as CHAR(20), the SQL properly casts the input value from VARCHAR(20) of the new UDF to a CHAR(20) value which is then passed to the same procedure 'CCDLIB/ANIPROCS($ISTOLLFREE)' as noted by the source function; i.e. the new UDF uses the same procedure used to implement the source\original UDF.

"This CREATE FUNCTION (Sourced) statement is used to create a user-defined function, based on another existing scalar or aggregate function, at the current server. ..."

Regards, Chuck

On 16 Apr 2012 08:40, Kurt Anderson wrote:
So creating a separate function expecting VarChar that pointed to a
separate procedure that expected a Varying field did the trick.

Thankfully SQL supports overloading, so the user can use the same
function name regardless of the file's field type.

Thanks for your input.

CRPence on Sunday, April 15, 2012 12:35 AM wrote:

On 13-Apr-2012 13:44 , Kurt Anderson wrote:
Here is my Function:
Create or Replace Function ccdlib/isTollFree ( ANI Char(20) )
returns Char(1)
language rpgle
no sql
parameter style general

The service program procedure's prototype:
// Is the Number Toll-Free?
D $isTollFree PR n
D NPA 20a Const


This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].