Re: Specify null parameter for SQL UDF

Hi Elvis,

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

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