Re: SQL UDF problem

[CRPence <CRPbottle@xxxxxxxxx>]

  AFaIK the prototype, having defined the return value typed as 8S00, 
should ensure that the "Return %dec(retVal:10:0);" is not an issue; i.e. 
the compiler should generate, effectively something like the following:

    returnValue8S00 = %dec(retVal:10:0) ; // intermediate result
    return returnValue8S00 ; // return this vs expression result

  Of course just returning retVal seems more appropriate and efficient, 
than forcing that intermediate step to be generated; so too, doing just 
one effective retVal=0 for initialization, versus doing both an INZ and 
RESET on the retVal.?

  And then I am left wondering how the "+2" fits in with the "# of days 
since 01/01/1900".  Seems something is amiss, given the input value of 
19000101 would return two versus zero.?  I suppose if the intention was 
to represent the number of days since 31-Dec-1899 inclusive of the date 
offered as input, then adding two to the difference makes sense.  But 
what about numeric values representing dates from before that apparent 
start date; almost seems the test for greater-than zero maybe should be 
greater than 19000100.?

  Seems to me that the procedure might have been coded much simpler 
with ¿[near] identical? results, as:

   monitor ;
    return %diff(%date(IsoDate:*iso):d'1899-12-30':*d) ;
   on-error *all ; // perhaps only 112 instead, is best.?
    return retVal ;
   endmon ;


Regards, Chuck

On 09-Dec-2013 02:05 -0800, Glenn Gundermann wrote:
> I'm not at a computer to test but could it be that your procedure
> is returning a numeric 8,0 and your %dec says 10,0?
>
> "Colpaert, Peter" Date: Mon, 9 Dec 2013 08:42:01 wrote:
>
> > <<SNIP>> It takes in a date in numeric ISO format, and should
> > return a date in excel format (# of days since 01/01/1900).
> >
> > However, it always returns zero.
> >
> > I re-created the function using the following syntax:
> >
> > create function colpaerp.isotoexcel(decimal(8, 0))
> > returns numeric(8, 0)
> > external name 'SRVPGMLIB/SQLUDF(ISOTOEXCEL)'
> > language rpgle
> > parameter style db2sql
> > no sql
> > deterministic
> > no external action
> > returns null on null input
> > allow parallel;
> >
> > This is the source of the function in the service program:
> >
> >       P IsoToexcel      b                   Export
> >       d IsoToExcel      pi             8s 0
> >       d   IsoDate                      8p 0 const
> >
> >       D Xls_StartDate   S               D   inz(d'1900-01-01')
> >       D retVal          s              8s 0 inz
> >        /free
> >            Reset retVal;
> >            test(de) *iso IsoDate;
> >            If %error;
> >            Else;
> >               If IsoDate > *zeros;
> >                  retVal = %diff(%date(IsoDate:*iso):xls_startdate:*d) + 2;
> >               EndIf;
> >            Endif;
> >
> >            Return %dec(retVal:10:0);
> >
> >        /end-free
> >       p IsoToExcel      e
> >
> > When I debugged the function, the retVal contained the correct
> > value, but in the SQL result it was zero.
> > <<SNIP>>