Re: Help with UDFs or stored procedures

On 12/18/06, Vernon Hamberg <vhamberg@xxxxxxxxxxx> wrote:

Dan - you are now in the world of using the SQL or the GENERAL WITH
NULLS parameter style - that means you have to add indicators for
NULL. This means you cannot use an existing service program as is.
Check the redbook for what the parameters need to be. Basically you
set an extra parameter to indicate a NULL.

Thanks Vern.  I scanned through the redbook.  Sorry, but this is over my
head, as in, the space shuttle is over my head.  ;-)  I found the PARAMETER
STYLE GENERAL WITH NULLS, but...   The example shows:

   CREATE FUNCTION SAMPLEDB01.GET_FX_RATE (
         ORIG_CCY CHAR(3) ,
         TRGT_CCY CHAR(3) ,
         FX_DATE DATE )
   RETURNS DECIMAL(10, 5)
   LANGUAGE COBOLLE
   SPECIFIC SAMPLEDB01.GET_FX_RATE01
   DETERMINISTIC
   READS SQL DATA
   RETURNS NULL ON NULL INPUT
   EXTERNAL NAME 'SAMPLEDB01/UDF_CBL(GET_FX_RATE)'
   PARAMETER STYLE GENERAL WITH NULLS;

and explains:

  For a UDF defined as in Example 15-10, the HLL
  program will have four input parameter and one
  output parameter, being the fourth a vector of
  null indicators corresponding to the null state
  for the first three parameters. The fifth
  parameter corresponds to the null state for the
  result the UDF will return.

SQL Reference says that, after the input parms specified on the on the
CREATE FUNCTION statement:
   -> An additional argument is passed for an indicator variable array.
   -> A parameter for the indicator variable for the result.
How are these two items defined in SQL?  Are they implicitly defined?

I guess I understand what I need to do in the RPG program and in the CREATE
FUNCTION statement, but what do I need to do to use the returned null
indicator in an SQL statement that uses the UDF?  Can I even use this in a
SELECT statement?  I did not see a usage example in that redbook, nor in the
SQL Reference or SQL Programming manuals.

TIA,
Dan