RE: SQL UDF not found MADNESS

["Schutte, Michael D" <Michael_Schutte@xxxxxxxxxxxx>]

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.