AW: Specify null parameter for SQL UDF

["Hauser, Birgitta" <Birgitta.Hauser@xxxxxxxxxxx>]

Hi Peter,

you have to overload your UDF.
That means you have to create a second UDF with the same name in the same
library, but without parameters.

create function UTLIB/ACCTGMONTH () 
       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)'

The new procedure gets its own signature and now can be called with or
without parameter.

Passing a NULL Parameter, does not mean the parameter is not passed, but a
NULL pointer is passed.

In RPG you'll use *OMIT to pass a NULL-Pointer-Parameter to an other
procedure.
In RPG you can check passed NULL-Pointers with ParmXYZ = *NULL.
If the parameter is optional and not passed, no pointer gets passed.
Checking a parameter that is not passed with = *NULL will cause a MCH3601
failure.
To check the number of parameter passed you'll use %Parms.
Note: Ommitted parameter are counted as passed! 

Just an information for your RPG procedure.
I'd add OPTIONS(*NOPASS) to all optional parameters.
This allows you to check the number of passed parameters using %PARMS.

 d AcctgMonth...                            
 d                 pr            10i 0      
 d  amDate                         d   const Options(*NoPass)
 d  amDateNull                   10i 0       Options(*NoPass)     
 d  amResultNull                 10i 0       Options(*NoPass)

Birgitta

"If you think education is expensive, try ignorance"
(Derek Bok)
 
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Peter Dow (ML)
Gesendet: Dienstag, 4. April 2006 21:35
An: MIDRANGE-L@xxxxxxxxxxxx
Betreff: 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> /