RE: Specify null parameter for SQL UDF

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Peter, bear with me as this is going to be a long post, but it should
resolve your issue (I hope).

My bad on suggesting that test without testing it myself.  Reason you're
getting the error with this is because it's looking for column named NULL in
SYSIBM1 and that of course does not exist.  
Better test would be this:

CREATE TABLE UTLIB/T1 (F1 DATE );
INSERT INTO UTLIB/T1 VALUES(NULL);
SELECT ACCTGMONTH(F1) from UTLIB/T1;

As you see I've created a table with a date field and then inserted a single
NULL field in it.  This is just so we can test NULL input to your UDF.

I have also created a dummy UDF that sort of does what you're doing in
yours.  I code in C so my sample is also in C but you should be able to
follow it as it's bare bones.  Here is the sample function:

int ACCTGMONTH(                                                    
   char  * in,                          /* first parm            */
   short * inNull,                      /* null indicator array  */
   short * outNull                      /* output null indicator */
)                                                                  
{                                                                  
  *outNull = 0;                         /* never NULL            */
  if (inNull[0] == -1)                  /* if NULL passed in     */
    return -1;                          /* output negative one   */
  else                                  /* if real value passed  */
    return 1;                           /* output positive one   */
}

I usually don't code with GENERAL WITH NULLS parameter style, but it is
relatively straight forward.
We have one input variable, so our inNull null indicator array of 2 byte
binaries only has one element.  I check if it's null by comparing it against
negative 1.
I don't have any logic for the output value but rather just output -1 if
NULL was passed in and 1 if incoming argument was non-NULL.

I don't code in RPG so I can't tell you if your function declaration is
valid or not, but that's likely where the problem is.

As I suspected, empty parenthesis are treated as a separate UDF (no
arguments hence different signature) so I get "not found" error when running
it like that.  
To handle this case, I suggest you create another function in the same
service program that handles this case and then register a separate UDF.
That way you can reuse the code base already in your SRVPGM.

Good luck.

Elvis

-----Original Message-----
Subject: Re: Specify null parameter for SQL UDF

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