Re: SQL UDF not found MADNESS -- MIDRANGE-L

Can you show us how you are trying to use it?

My guess is that either
1) Your parms don't match
2) You're using SQL naming instead of system naming (or vice versa :)

For #1, try to CAST the field to match

select TRUEDATE(cast(fldCC as dec(2,0))
, cast(fldYY as dec(2,0))
, cast(fldMM as dec(2,0))
, cast(fldDD as dec(2,0))
)
from myfile

For #2
Note that there are differences in how the system searches for
unqualified functions depending on which naming convention you use. Or
more specifically, the defaults for the PATH used are different.
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/sqlp/rbafyudfduvc.htm
(snippet below)
Full name of a function

The full name of a function using *SQL naming is <schema-name>.<function-name>.

The full name of a function in *SYS naming is
<schema-name>/<function-name>. Function names cannot be qualified
using *SYS naming in DML statements.

You can use this full name anywhere you refer to a function. For example:

QGPL.SNOWBLOWER_SIZE SMITH.FOO QSYS2.SUBSTR QSYS2.FLOOR

However, you may also omit the <schema-name>., in which case, DB2 must
determine the function to which you are referring. For example:

SNOWBLOWER_SIZE FOO SUBSTR FLOOR

Path

The concept of path is central to DB2's resolution of unqualified
references that occur when schema-name is not specified. The path is
an ordered list of schema names that is used for resolving unqualified
references to UDFs and UDTs. In cases where a function reference
matches a function in more than one schema in the path, the order of
the schemas in the path is used to resolve this match. The path is
established by means of the SQLPATH option on the precompile commands
for static SQL. The path is set by the SET PATH statement for dynamic
SQL. When the first SQL statement that runs in an activation group
runs with SQL naming, the path has the following default value:

"QSYS","QSYS2","<ID>"

This applies to both static and dynamic SQL, where <ID> represents the
current statement authorization ID.

When the first SQL statement in an activation group runs with system
naming, the default path is *LIBL.

HTH,
Charles

On Thu, Aug 4, 2011 at 3:16 PM, Dan <dan27649@xxxxxxxxx> wrote:

I'm at my wit's end. This was working the last time I used it a few months
ago. Now I'm getting SQL0204 "<function> in *LIBL type *N not found." The
library containing both the service program and the function is at the top
of the library list. (FWIW, we're at V6R1.)

The function is called TRUEDATE. It's found in QSYS2/SYSFUNCTIONS. The
service program by the same name also exists in the library list. After
first getting this message, I deleted the service program and module
objects, dropped the function (DROP TRUEDATE), and recreated all from
scratch. Same result. Hair accumulating on floor.

(For the curious, this function takes in four 2-digit values (CC, YY, MM, &
DD) and returns a date. The legacy tables here are rife with dates
represented by four 2-digit fields,)

Following is the source, with the the command used to create the service
program shown as a comment in the RPGLE source, and the SQL statement to
create the UDF at the very end:

TRUEDATEPR TrueDate f(): Prototype

d TrueDate pr d
d p_CenturyIn 2p 0
d p_YearIn 2p 0
d p_MonthIn 2p 0
d p_DayIn 2p 0
* The null indicator fields below mandate option PARAMETER STYLE GENERAL
WITH NULLS
d pni_Input4 5i 0 Dim( 4 )
d pni_Result 5i 0

TRUEDATEM TrueDate f(): Module

* TrueDate - receives 4 parms (century, year, month, & day, all 2 digits)
* and returns an *ISO format date field
h Option( *SrcStmt : *NoDebugIO )
**#$%COM: DbgView( *All )
**#$%OBJ: *MODULE
**#$%PST: CRTSRVPGM SRVPGM(DANDEV/TRUEDATE) MODULE(DANDEV/TRUEDATEM) +
**#$%PST: EXPORT(*SRCFILE) SRCFILE(DANDEV/DANDEV) SRCMBR(*SRVPGM) +
**#$%PST: TEXT('TrueDate: Four 2-digit date fields in, date out') +
**#$%PST: AUT(*ALL)
*
* See SQL source member TRUEDATECF for the script to Create the Function
d/copy DANDEV/DANDEV,TrueDatePr
p TrueDate b export
d TrueDate pi d DatFmt( *ISO )
d p_CenturyIn 2p 0
d p_YearIn 2p 0
d p_MonthIn 2p 0
d p_DayIn 2p 0
d pni_Input4 5i 0 Dim( 4 )
d pni_Result 5i 0

d ds
d DateIn 8s 0
d inCentury 2s 0 Overlay( DateIn : 1 )
d inYear 2s 0 Overlay( DateIn : 3 )
d inMonth 2s 0 Overlay( DateIn : 5 )
d inDay 2s 0 Overlay( DateIn : 7 )

d DateOut s d Inz DatFmt( *ISO )

/free
inCentury = p_CenturyIn ;
inYear = p_YearIn ;
inMonth = p_MonthIn ;
inDay = p_DayIn ;
Test(DE) *iso DateIn ;
If %error ;
// Return *Null ; <== doesn't like; *NULL not same type as prototype
pni_Result = -1 ; // Null Indicator is set ON
Else ;
DateOut = %date( DateIn : *iso ) ;
pni_Result = 0 ; // Null Indicator is set OFF
Endif ;

*inLR = *off ;
Return DateOut ;
/end-free
p e

TRUEDATE Export source for TrueDate

STRPGMEXP SIGNATURE('TrueDate20110804')
EXPORT SYMBOL(TRUEDATE)
ENDPGMEXP

The SQL statement to create the function:

CREATE FUNCTION DANDEV/TRUEDATE (CC DEC(2,0), YY DEC(2,0),
MM DEC(2,0), DD DEC(2,0) )
RETURNS DATE
LANGUAGE RPGLE
PARAMETER STYLE GENERAL WITH NULLS
DETERMINISTIC
NO SQL
RETURNS NULL ON NULL INPUT
NO EXTERNAL ACTION
NOT FENCED
EXTERNAL NAME 'DANDEV/TRUEDATE(TRUEDATE)'
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.