RE: Specify null parameter for SQL UDF

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

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>