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.
_CREATE FUNCTION (Sourced)_
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstcfso.htm
"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.
<<SNIP>>
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:
<<SNIP>>
Here is my Function:
Create or Replace Function ccdlib/isTollFree ( ANI Char(20) )
returns Char(1)
language rpgle
no sql
external name 'CCDLIB/ANIPROCS($ISTOLLFREE)'
parameter style general
The service program procedure's prototype:
// Is the Number Toll-Free?
D $isTollFree PR n
D NPA 20a Const
<<SNIP>>
midrange.com
