There are 366 days in leap years. Why hard code 365, when we have data functions. EndDate = CurrentDate + %Years(1).
Original Message:
Sent: Mon October 03, 2022 08:51 AM
From: Paolo Salvatore
Subject: Sql External table function
Hi Christian,
thank you for your suggestion.
------------------------------
Paolo Salvatore
Original Message:
Sent: Mon October 03, 2022 02:37 AM
From: Christian Jorgensen
Subject: Sql External table function
Hi Paolo.
You can add the keyword STATIC to any variable inside a subprocedure, and the value will be kept between calls.
So you can move GetNextYearDate
and GetNextYearIdx
into procedure GetNextYear
and just add STATIC. They will be initialized on the first call.
It is a good practice to avoid global variables if possible.
Best regards,
Christian
------------------------------
Christian Jorgensen
IT System Administrator
Network of Music Partners A/S
Original Message:
Sent: Fri September 30, 2022 10:55 AM
From: Paolo Salvatore
Subject: Sql External table function
Hi,
I found a solutions, but I don't know if it is correct...
Here's the new code:
**freeCtl-Opt NoMain Expropts(*AlwBlankNum) Option(*NoUnRef :*SrcStmt :*NoDebugIo) DatFmt(*Iso) TimFmt(*Iso) Debug(*Constants) AlwNull(*UsrCtl) DftName(TESTSRVPGM) Text('Modulo per service program TESTSRVPGM');Dcl-S GetNextYearIdx Int(5);Dcl-S GetNextYearDate Date;// ____________________________________________________________________________Dcl-Proc GetNextYear Export; Dcl-Pi GetNextYear; StartDate Date; // Input Data DaysToAdd Int(10); // Input Data ReturnDate Date; // Return Data n_StartDate Int(5) Const; // Input Data null n_DaysToAdd Int(5); // Input Data n_ReturnDate Int(5); // Return Data null State Char(5); Function VarChar(517) Const; Specific VarChar(128) Const; errorMsg VarChar(1000); CallType Int(10) Const; End-Pi GetNextYear; // ____________________________________________________________________________ Dcl-C CALL_OPEN -1; Dcl-C CALL_FETCH 0; Dcl-C CALL_CLOSE 1; Dcl-C PARM_NULL -1; Dcl-C PARM_NOTNULL 0; // ____________________________________________________________________________ Select; When (CallType = CALL_OPEN); If (n_StartDate = PARM_NULL); StartDate = %Date(); EndIf; If (n_DaysToAdd = PARM_NULL); DaysToAdd = 365; EndIf; GetNextYearDate = StartDate; GetNextYearIdx = 0; When (CallType = CALL_FETCH); ReturnDate = GetNextYearDate; ReturnDate += %Days(1); If (GetNextYearIdx >= DaysToAdd); State = '02000'; Return; EndIf; GetNextYearIdx += 1; GetNextYearDate = ReturnDate; When (CallType = CALL_CLOSE); Reset GetNextYearDate; Reset GetNextYearIdx; State = '00000'; Return; EndSl;End-Proc GetNextYear;
And the new function:
Create Or Replace Function mylib/Getnextyear ( Datapartenza Date, DaysToAdd Int ) Returns Table ( Datanext Date ) External Name 'MYLIB/TESTSRVPGM(GETNEXTYEAR)' Language Rpgle Program Type Sub Parameter Style sql No Sql Not Deterministic Disallow Parallel;
With this review it works, but I have a problem with variabile GetNextYearDate, GetNextYearIdx.
Is it correct to define for global scope? If I declare this variable in my subprocedure, every time the function is called those variables are initialized, so is there some trick?
Many thanks
------------------------------
Paolo Salvatore
Original Message:
Sent: Fri September 30, 2022 09:14 AM
From: Paolo Salvatore
Subject: Sql External table function
Hi,
looking to Scott Klement site I found this:
https://www.scottklement.com/presentations/RPG%20User%20Defined%20Functions%20&%20Table%20Functions.pdf
So I change my procedure to this:
**freeCtl-Opt NoMain Expropts(*AlwBlankNum) Option(*NoUnRef :*SrcStmt :*NoDebugIo) DatFmt(*Iso) TimFmt(*Iso) Debug(*Constants) AlwNull(*UsrCtl) DftName(TESTSRVPGM) Text('Modulo per service program TESTSRVPGM');// ____________________________________________________________________________Dcl-Proc GetNextYear Export; Dcl-Pi GetNextYear; StartDate Date Const; // Input Data ReturnDate Date; // Return Data n_StartDate Int(5) Const; // Input Data null n_ReturnDate Int(5); // Return Data null State Char(5); Function VarChar(517) Const; Specific VarChar(128) Const; errorMsg VarChar(1000); CallType Int(10) Const; End-Pi GetNextYear; // ____________________________________________________________________________ Dcl-C CALL_OPEN -1; Dcl-C CALL_FETCH 0; Dcl-C CALL_CLOSE 1; Dcl-C PARM_NULL -1; Dcl-C PARM_NOTNULL 0; Dcl-S Idx Int(5); If (n_StartDate=PARM_NULL); State = '38999'; errorMsg = 'StartDate is mandatory'; return; EndIf; Select; When (CallType = CALL_OPEN); ReturnDate = StartDate; Idx = 0; When (CallType = CALL_FETCH); ReturnDate += %Days(1); Idx += 1; If (Idx = 365); State = '02000'; Return; EndIf; When (CallType = CALL_CLOSE); State = '00000'; Return; EndSl;End-Proc GetNextYear;
Then I recreate the service program:
CRTSRVPGM SRVPGM(MYLIB/TESTSRVPGM) EXPORT(*ALL)
and the function:
Create Or Replace Function MyLib/Getnextyear ( Datapartenza Date ) Returns Table ( Datanext Date ) External Name 'MYLIB/TESTSRVPGM(GETNEXTYEAR)' Language Rpgle Program Type Sub Parameter Style Db2sql No Sql Not Deterministic Disallow Parallel;
But something doesn't work, it never pass from the statement:
When (CallType = CALL_OPEN);
ReturnDate = StartDate;
Idx = 0;
why?
Many thanks
------------------------------
Paolo Salvatore
Original Message:
Sent: Fri September 30, 2022 06:09 AM
From: Paolo Salvatore
Subject: Sql External table function
Hi,
I need a table with one row per day for one year, I try with an external function that call an RPG and return a DS dim(365). But something doesn't work, I create a sample:
Module:
**freeCtl-Opt NoMain Expropts(*AlwBlankNum) Option(*NoUnRef :*SrcStmt :*NoDebugIo) DatFmt(*Iso) TimFmt(*Iso) Debug(*Constants) AlwNull(*UsrCtl) DftName(TESTSRVPGM) Text('Modulo per service program TESTSRVPGM');// ____________________________________________________________________________Dcl-Proc GetNextYear Export; Dcl-Pi GetNextYear LikeDs(NextYear_Template) Dim(365); StartDate Packed(8 :0) Const; // Data di Partenza End-Pi GetNextYear; // ____________________________________________________________________________ Dcl-Ds NextYear LikeDs(NextYear_Template) Dim(365); Dcl-S Idx Int(5); // ____________________________________________________________________________ Dcl-Ds NextYear_Template Qualified Template Dim(365); Data Packed(8); End-Ds NextYear_Template; Reset NextYear; NextYear(1).Data = StartDate; For Idx = 2 to 365; NextYear(Idx).Data = %Dec(%Date(NextYear(Idx - 1).Data) + %Days(1)); EndFor; Return NextYear(*);End-Proc GetNextYear;
Compilation command:
CRTRPGMOD MODULE(MYLIB/testsrvpgm) SRCSTMF('./qrpglesrc/testsrvpgm.rpgmod') OPTION(*EVENTF) TGTCCSID(*JOB)
Then I create a service program:
CRTSRVPGM SRVPGM(MYLIB/TESTSRVPGM) EXPORT(*ALL)
Sql Function:
Create Or Replace Function MYLIB/Getnextyear ( Datapartenza decimal(8, 0) ) Returns Table ( Datanext Decimal(8, 0) ) External Name 'MYLIB/TESTSRVPGM(GETNEXTYEAR)' Language Rpgle PARAMETER STYLE SQL No Sql DETERMINISTIC NO EXTERNAL ACTION NOT FENCED SCRATCHPAD FINAL CALL DISALLOW PARALLEL CARDINALITY 365;
Then trying in strsql I, it return to me MCH3601
Select * From Table ( MYLIB/getnextyear(20220930) )
Any Idea?
Is it possible what I'm trying to do?
Do you have a better idea?
Many thanks
------------------------------
Paolo Salvatore
------------------------------