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

When calling the procedures you have to make sure that you are passing exactly what the procedure is expecting. The reason you are getting not found is message is likely because of parameter mismatch. SQL will look for overload procedure/UDF that match what you passed.

For example.

Create function qtemp/test ( decparm dec(2, 0), intparm integer ) return dec(2,0) ...

When you call test...

Select * from file
Where test (2, 2) = 1

SQL will search for a function where both parms are defined as decimals. In which case it wouldn't be found.

However,

Select * from file
Where test(2, int(2) ) = 1

Would be found. Because now it's looking for a procedure where the first parm is decimal and the second is integer.

That's my only suggestion, make sure you are passing the type that the UDF is expecting.

-----Original Message-----
From: midrange-l-bounces+michael_schutte=bobevans.com@xxxxxxxxxxxx [mailto:midrange-l-bounces+michael_schutte=bobevans.com@xxxxxxxxxxxx] On Behalf Of Dan
Sent: Thursday, August 04, 2011 4:17 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL UDF not found MADNESS

The session attributes in the STRSQL session I am using has

Naming convention . . . . . . *SYS

On Thu, Aug 4, 2011 at 3:33 PM, Morgan, Paul <Paul.Morgan@xxxxxxxxxxx>wrote:

Dan,

Is the calling program or SQL source using the library list (*SYS naming)
or the SQL path (QSYS/QSYS2/RDB Library with *SQL naming) to find the UDF?

Paul Morgan

Principal Programmer Analyst
IT Supply Chain/Replenishment

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan
Sent: Thursday, August 04, 2011 3:16 PM
To: Midrange Systems Technical Discussion
Subject: SQL UDF not found MADNESS

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.

--
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.

--
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.

________________________________

Notice from Bob Evans Farms, Inc: This e-mail message, including any attachments, may contain confidential information that is intended only for the person or entity to which it is addressed. Any unauthorized review, use, disclosure or distribution is strictly prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message and any attachments.