Re: SQL UDF problem

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