IBM i Global

 View Only
Expand all | Collapse all

Sql External table function

  • 1.  Sql External table function

    Posted Fri September 30, 2022 06:10 AM
    Edited by Paolo Salvatore Fri September 30, 2022 06:11 AM
    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:

    **free
Ctl-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
    ------------------------------


  • 2.  RE: Sql External table function

    Posted Fri September 30, 2022 09:15 AM

    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:

    **free
Ctl-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


  • 3.  RE: Sql External table function

    Posted Fri September 30, 2022 10:56 AM
    Edited by Paolo Salvatore Fri September 30, 2022 10:56 AM
    Hi, 

    I found a solutions, but I don't know if it is correct... 

    Here's the new code:

    **free
Ctl-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


  • 4.  RE: Sql External table function

    IBM Champion
    Posted Mon October 03, 2022 02:38 AM
    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


  • 5.  RE: Sql External table function

    Posted Mon October 03, 2022 08:51 AM
    Hi Christian, 

    thank you for your suggestion.


    ------------------------------
    Paolo Salvatore
    ------------------------------

    Original Message


  • 6.  RE: Sql External table function

    Posted Mon October 03, 2022 03:50 PM
    There are 366 days in leap years. Why hard code 365, when we have data functions. EndDate = CurrentDate + %Years(1).

    ------------------------------
    Chris McNierney
    ------------------------------

    Original Message


  • 7.  RE: Sql External table function

    IBM Champion
    Posted Wed October 05, 2022 07:57 AM
    Why not use SQL all the way? 

    create or replace function yxdb.days_in_year (
       year_YYYY char(4)
    )
    returns table ( date_in_year date )
    set option output=*print, commit=*none, dbgview = *source --list
    begin

       declare first_date date;
       set first_date = year_YYYY concat '-01-01';

       while year(first_date) = year_YYYY do
          pipe (first_date);
          set first_date = first_date + 1 day;
       end while;
       return;
    end;

    -- Test case
    select * from table(
       yxdb.days_in_year (
          year_YYYY => '2020'
       )
    );

    ------------------------------
    Niels Liisberg
    ------------------------------

    Original Message


  • 8.  RE: Sql External table function

    Posted Thu October 06, 2022 04:56 AM
    Using SQL, you can also use a quick recursive relation to generate any calendar on the fly

    WITH calendar( end, dayvar ) as (
    VALUES ( DATE('1900-12-31'), DATE('1900-01-01') )
    UNION ALL
    SELECT end, dayvar + 1 DAYS from calendar where dayvar < end
    )
    select dayvar from calendar

    ------------------------------
    --ft
    ------------------------------

    Original Message


  • 9.  RE: Sql External table function

    Posted Mon October 10, 2022 09:57 AM
    Many thanks to all!

    this is my prefered solutions.

    Bye!!

    ------------------------------
    Paolo Salvatore
    ------------------------------

    Original Message


  • 10.  RE: Sql External table function

    IBM Champion
    Posted Thu October 06, 2022 02:11 AM
    You can read this article https://www.ibm.com/support/pages/sites/default/files/inline-files/$FILE/Converting%20Legacy%20Date%20Fields%20to%20DB2%20Web%20Query%20Dates%20Part%20II.pdf. The conversion date table is explained into the redbook SG24-7214-01 "Getting Started with DB2 Web Query for i" (appendix B p. 542-552).

    ------------------------------
    Marco Riva
    senior developer
    Sirio Informatica e Sistemi SpA
    Milano
    mriva@sirio-is.it
    ------------------------------

    Original Message


  • 11.  RE: Sql External table function

    IBM Champion
    Posted Thu October 06, 2022 07:38 PM
    Dear Marco

    The URL you provided leads me to this error page.  Does it actually work for you?



    ------------------------------
    Right action is better than knowledge; but in order to do what is right, we must know what is right.
    -- Charlemagne

    Satid Singkorapoom
    ------------------------------

    Original Message


  • 12.  RE: Sql External table function

    IBM Champion
    Posted Fri October 07, 2022 04:27 AM
      |   view attached

    Ops. I don't know why the link doesn't work.

    https://www.ibm.com/support/pages/sites/default/files/inline-files/$FILE/Converting%20Legacy%20Date%20Fields%20to%20DB2%20Web%20Query%20Dates%20Part%20II.pdf

    Otherwise you can search on Google the text "TechTip: Converting Legacy Date Fields to DB2 Web Query Dates, Part II"

    I attach the pdf file.

    Bye.



    ------------------------------
    Marco Riva
    senior developer
    Sirio Informatica e Sistemi SpA
    Milano
    mriva@sirio-is.it
    ------------------------------

    Original Message


  • 13.  RE: Sql External table function

    Posted Thu October 06, 2022 09:46 AM
    Hello Paolo,
    did you try sql only ?
    something like that (not tested)


    create procedure myproc () language sql set option datfmt = *iso , dbgview=*list
    begin     declare mydate date ;
    set mydate = '2022-01-01' ;
    repeat
    insert into mycalendar (thedatecolumn) values (mydate );
    set mydate = mydate + 1 day ;
    until mydate > '2022-12-31'
    end repeat ; end

    to be improved to add a parameter for the year

    ------------------------------
    jean-paul lamontre
    ------------------------------

    Original Message