RE: Specify null parameter for SQL UDF -- MIDRANGE-L

This looks like a time when I can learn something about SQL UDFs that I
didn't know before. If the UDF registration script is complete, it looks
like you can create a UDF that accepts parameters without defining those
parameters. Is that correct?

For an RPGLE service program to accept null parameters, I thought you
had to specify Options(*Omit) on those parameters. I don't see that
keyword on the AcctgMonth parameters. Does it work differently when you
wrap the service program in a UDF?

Thanks for any answers you can provide,
Roger Mackie

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
Sent: Tuesday, April 04, 2006 4:07 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Specify null parameter for SQL UDF

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>



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