Re: Specify null parameter for SQL UDF

["Peter Dow (ML)" <maillist@xxxxxxxxxxxxxxx>]

Hi Elvis,

I tried it and got "Column NULL not in specified tables."

Another wrapper would work, but then what's the point of "CALLED ON NULL 
INPUT"?  There should be a way to use it...shouldn't there?
*
Peter Dow* /
Dow Software Services, Inc.
909 793-9050
pdow@xxxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxxx> /

Elvis Budimlic wrote:
> I think your UDF syntax is fine, especially being that you did specify
> CALLED ON NULL INPUT.
>
> Perhaps empty parenthesis are not treated as NULL input?
> Test it with: 
>    SELECT ACCTGMONTH(NULL) FROM SYSIBM/SYSDUMMY1
> and verify if that's the case or not.
>
> If it is, you'll have to create another wrapper UDF that takes no parameters
> and perhaps maps to the original one.
>
> Elvis
>
> -----Original Message-----
> Subject: Specify null parameter for SQL UDF
>
> Hi Everyone,
>
> How do I call a UDF with a null parameter? I have a UDF created to 
> return the accounting month for a given date (the only parameter), but 
> if no parameter is given, it will return the current accounting month.
>
> It works fine when I pass it a date, e.g.
>
> SELECT ACCTGMONTH(CURRENT_DATE) FROM ANYFILE
>
> but complains (says it cannot find ACCTGMONTH) if I use it like this:
>
> SELECT ACCTGMONTH() FROM ANYFILE
>
> I understand that it identifies a UDF based on the name and the input 
> parameter types, but I don't understand how to tell it a particular 
> parameter may be optional.
>
> I created it with the following:
>
> create function UTLIB/ACCTGMONTH (date) returns integer
>        language RPGLE                                 
>        parameter style GENERAL WITH NULLS             
>        not deterministic                              
>        no SQL                                
>        called on null input                           
>        no external action                             
>        external name 'UTLIB/SVCBUS(ACCTGMONTH)'
>
> ACCTGMONTH is a procedure within a service program, with prototype like 
> this:
>
>  d AcctgMonth...                            
>  d                 pr            10i 0      
>  d  amDate                         d   const
>  d  amDateNull                   10i 0      
>  d  amResultNull                 10i 0
>
> *Peter Dow* /
> Dow Software Services, Inc.
> 909 793-9050
> pdow@xxxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxxx>
>
>
>
>