Hi Chuck,
The calculation itself was give to me by my good friend Mr Google.
And as you know, Excel doesn't like dates before 1/1/1900.
Anyway, my issue was solved by changing the parameter style from DB2SQL to GENERAL.
Met vriendelijke groeten / Best regards,
Peter Colpaert
Software Engineer - PLM Development Team
IT Operations Cluster Benelux, Philips IT
Philips Consumer Luminaires
Industrieterrein Satenrozen 11, 2550 Kontich, Belgium
Tel: (+32) 3/459 13 17
Email: Peter.Colpaert@xxxxxxxxxxx
Working from home on Wednesdays
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: maandag 9 december 2013 13:44
To: rpg400-l@xxxxxxxxxxxx
Subject: 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>>
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
________________________________
The information contained in this message may be confidential and legally protected under applicable law. The message is intended solely for the addressee(s). If you are not the intended recipient, you are hereby notified that any use, forwarding, dissemination, or reproduction of this message is strictly prohibited and may be unlawful. If you are not the intended recipient, please contact the sender by return e-mail and destroy all copies of the original message.
midrange.com
