My new UDTFs does not work

[allthom]

I'm having various difficulties creating what appears to me to be fairly simple UDTFs and I can't figure out exactly what's going on, it just doesn't work.
The scenario in which I move is relatively simple. I have a table/PF built via DDL that contains the payroll entries of a company. Given an identification number of a pay slip, I want to read all the records present in the pay slip table/PF, add some entries and return the sums "horizontally" rather than "vertically".

The repeated error is that the file to be read is not open, and therefore each fetch cycle in which the calculation routine is repeated is never carried out because the previous doOpen routine was not carried out.

Hope someone can give me any hint to understand what is going wrong with this one

Here's my code:

Code: Select all

**free
ctl-opt noMain;
ctl-opt alwnull(*usrctl);

dcl-pr cedolone extproc('cedolone');
  idPratica int(10) const;
  emolumenti packed(11:2);
  spese packed(11:2);
  inpsAzi packed(11:2);
  inpsCol packed(11:2);
  inailAzi packed(11:2);
  inailColl packed(11:2);
  irpef packed(11:2);
  addRega packed(11:2);
  addComi packed(11:2);
  arrAtt packed(11:2);
  arrPass packed(11:2);
  netto packed(11:2);
  pignoramenti packed(11:2);
  trattenute packed(11:2);
  n_idPratica int(5) const;
  n_emolumenti int(5);
  n_spese int(5);
  n_inpsAzi int(5);
  n_inpsCol int(5);
  n_inailAzi int(5);
  n_inailColl int(5);
  n_irpef int(5);
  n_addRega int(5);
  n_addComi int(5);
  n_arrAtt int(5);
  n_arrPass int(5);
  n_netto int(5);
  n_pignoramenti int(5);
  n_trattenute int(5);
  state char(5);
  Function varchar(517) const;
  Specific varchar(128) const;
  errorMsg varchar(1000);
  callType int(10) const;
end-pr;

dcl-proc cedolone export;
    dcl-pi *n ;
      idPratica int(10) const;
      emolumenti packed(11:2);
      spese packed(11:2);
      inpsAzi packed(11:2);
      inpsCol packed(11:2);
      inailAzi packed(11:2);
      inailColl packed(11:2);
      irpef packed(11:2);
      addRega packed(11:2);
      addComi packed(11:2);
      arrAtt packed(11:2);
      arrPass packed(11:2);
      netto packed(11:2);
      pignoramenti packed(11:2);
      trattenute packed(11:2);
      n_idPratica int(5) const;
      n_emolumenti int(5);
      n_spese int(5);
      n_inpsAzi int(5);
      n_inpsCol int(5);
      n_inailAzi int(5);
      n_inailColl int(5);
      n_irpef int(5);
      n_addRega int(5);
      n_addComi int(5);
      n_arrAtt int(5);
      n_arrPass int(5);
      n_netto int(5);
      n_pignoramenti int(5);
      n_trattenute int(5);
      state char(5);
      Function varchar(517) const;
      Specific varchar(128) const;
      errorMsg varchar(1000);
      callType int(10) const;
    end-pi;

    dcl-f copdc00f usage(*input:*output) keyed rename(COPDC00F:copdc) usropn;
    dcl-ds tCopdc likerec(COPDC) inz;
    dcl-ds tKey likerec(copdc : *key) Inz;

    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;
        
    //svolgimento

    if idPratica=PARM_NULL or idPratica=0;
      state = '38999';
      errorMsg = 'Indicare un numero pratica valido';
      return;
    else;
      tKey.pdcprid=%dec(idPratica : 13 : 0);
      tkey.pdccvoce=*blanks;
    endif;
    
        
    select;
      when CallType = CALL_OPEN;
        exsr doOpen;
      when CallType = CALL_FETCH;
        exsr doFetch;
      when CallType = CALL_CLOSE;
        exsr doClose;
        return;
    endsl;

    begsr doOpen;
      if not %open(copdc00f);
        open copdc00f;
      endif;
      
      setll %Kds(tKey:1) copdc00f;
      if not %equal;
        state = '38998';
        errorMsg = 'La pratica numero ' + %char(tkey.PDCPRID) + ' non è stata trovata';
        return;
      endif;
    endsr;

    begsr doFetch;
      reset tCopdc;

      reade %kds(tkey:1) copdc00f tCopdc;

      if %eof(copdc00f);
        state = '02000';
        leavesr;
      endif;

      select;
        when tCopdc.PDCTVOCE=1; //emolumenti
          n_emolumenti=PARM_NOTNULL;
          emolumenti=tCopdc.PDCIVOCE;
        when tCopdc.PDCTVOCE=2; //spese
          n_spese=PARM_NOTNULL;
          spese=tCopdc.PDCIVOCE;
        when tCopdc.PDCTVOCE=3; //INPS Azienda
          n_inpsAzi=PARM_NOTNULL;
          inpsAzi=tCopdc.PDCIVOCE;
        when tCopdc.PDCTVOCE=4; //INPS Collaboraori
          n_inpsCol=PARM_NOTNULL;
          inpsCol=tCopdc.PDCIVOCE;
        when tCopdc.PDCTVOCE=5; //INAIL Azienda
          n_inailAzi=PARM_NOTNULL;
          inailAzi=tCopdc.PDCIVOCE;
        when tCopdc.PDCTVOCE=6; //INAIL Collaboratori
          n_inailColl=PARM_NOTNULL;
          inailColl=tCopdc.PDCIVOCE;
        when tCopdc.PDCTVOCE=7; //IRPEF
          n_irpef=PARM_NOTNULL;
          irpef=tCopdc.PDCIVOCE;
        when tCopdc.PDCTVOCE=8; //Addizionale Regionale
          n_addRega=PARM_NOTNULL;
          addRega=tCopdc.PDCIVOCE;
        when tCopdc.PDCTVOCE=9; //Addizione Comunale
          n_addComi=tCopdc.PDCIVOCE;
          addComi=tCopdc.PDCIVOCE;
        when tCopdc.PDCTVOCE=10; //Arrotondamenti attivi
          n_arrAtt=PARM_NOTNULL;
          arrAtt=tCopdc.PDCIVOCE;
        when tCopdc.PDCTVOCE=11; //Arrotondamenti passivi
          n_arrPass=PARM_NOTNULL;
          arrPass=tCopdc.PDCIVOCE;
        when tCopdc.PDCTVOCE=12; //Netto a pagare
          n_netto=PARM_NOTNULL;
          netto=tCopdc.PDCIVOCE;
        when tCopdc.PDCTVOCE=13; //Recupero pignoramenti
          n_pignoramenti=PARM_NOTNULL;
          pignoramenti=tCopdc.PDCIVOCE;
        when tCopdc.PDCTVOCE=14; //Trattenute varie
          n_trattenute=PARM_NOTNULL;
          trattenute=tCopdc.PDCIVOCE;
        other;
          state = '39998';
          errorMsg='Tipo voce sconosciuto';
          return;
      endsl;
    endsr;

    begsr doClose;
      close copdc00f;
    endsr;

end-proc;

here is the create function statement

Code: Select all

Create Function lnd_dw.cedolone( idPratica integer)
Returns Table
(
  emolumenti decimal(11, 2),
  spese decimal(11, 2),
  inpsAzi decimal(11, 2),
  inpsCol decimal(11, 2),
  inailAzi decimal(11, 2),
  inailColl decimal(11, 2),
  irpef decimal(11, 2),
  addRega decimal(11, 2),
  addComi decimal(11, 2),
  arrAtt decimal(11, 2),
  arrPass decimal(11, 2),
  netto decimal(11, 2),
  pignoramenti decimal(11, 2),
  trattenute decimal(11, 2)
)
external name 'LND_DW/CEDOLONE(cedolone)'
language rpgle
parameter style db2sql
no sql
not deterministic
disallow parallel

When i try to use the udtfs this way the thing does not work, the error i see is RNX1211 The rpg proc tried to READE on file COPDC00F while the file was closed. Be aware the file has been created via ddl (create table...) and is not dds based.

[Scott Klement]

It looks like your file (copdc00f) is declared inside the subprocedure. Therefore it will be automatically closed when the subprocedure returns.

Move your 'dcl-f' so that it is before the first 'dcl-proc.'

[allthom]

It looks like your file (copdc00f) is declared inside the subprocedure. Therefore it will be automatically closed when the subprocedure returns.

Move your 'dcl-f' so that it is before the first 'dcl-proc.'

It works almost, but i don't understand: what difference does it make the position of the dcl-f?
One last thing I'm getting this type of result (look at the attachements) and i don't unserstand why:


i was expecting to see all the numbers in only one row.
TIA Again Scott

[Scott Klement]

It works almost, but i don't understand: what difference does it make the position of the dcl-f?

When you define something inside a subprocedure, it only exists while that subprocedure is running -- that means that when the subprocedure ends, the file is closed and the memory it was using is returned to the system to be used for other things. (You can work around this by declaring it as "static" inside the subprocedure, or by declaring it global to the module.)

One last thing I'm getting this type of result (look at the attachements) and i don't unserstand why:

I am not familiar with your application, your business or your database, so I cannot tell you how your business logic is meant to work. But from looking at the code, it appears to be setting only one field at a time in a SELECT/WHEN construct.

[allthom]

I am not familiar with your application, your business or your database, so I cannot tell you how your business logic is meant to work. But from looking at the code, it appears to be setting only one field at a time in a SELECT/WHEN construct.

it seems like I need to study better how the loop and udtfs logic work.
in my scenario I have a simple structured table/pf with two key fields.
the first of these keys is the parameter that I pass with the call and when I find records present for that key there can be from 1 to n records in the file.
so if I understand correctly in the dofetch routine I have to read all the records for that key on the first call (with a dow or dou loop) and at the end set the state to a value of 02000, instead of allowing dofetch to be called n times until the eof Is reached.

[Scott Klement]

so if I understand correctly in the dofetch routine I have to read all the records for that key on the first call (with a dow or dou loop) and at the end set the state to a value of 02000, instead of allowing dofetch to be called n times until the eof Is reached.

Assuming that you want to return one row from the UDTF for each record that is in the copdc00f, then you would only read ONE row in the doFetch routine. Your subprocedure will be called repeatedly, once for each row.

[allthom]

Assuming that you want to return one row from the UDTF for each record that is in the copdc00f, then you would only read ONE row in the doFetch routine. Your subprocedure will be called repeatedly, once for each row.

No, i want to return only one row with all the meaningful valute in it

[Scott Klement]

okay... then you'd want to fill in all of the data you want to return in that one row the first time that doFetch is called. The second time doFetch is called, you'd set the SQL State to '02000' to indicate that there aren't any other rows.

[allthom]

Is there a way to debug the srvpgm procedure while working in a "sql environment"? I.e. while running strsql command?

[Scott Klement]

Not sure about STRSQL.

If you use "Run SQL Scripts" you can use the "System Debugger" option off of the Run menu. This works very well for debugging UDTFs, is included with ACS (amongst other things) and is very easy to use.