openedge - How can I get the last element of multiple items in Progress?

Question

I'm trying to get the last element in some tables. This program is based on a SQL one, and the function used on the SQL is the MAX(). but using it on Progress is not working properly. My code is like the following.

FOR EACH nota-fiscal
    WHERE nota-fiscal.dt-emis-nota > TODAY - pd-dias
    AND nota-fiscal.cod-emitente <> 101 AND nota-fiscal.cod-emitente <> 102 NO-LOCK, 
        EACH repres 
        WHERE nota-fiscal.cod-rep = repres.cod-rep 
        AND repres.cod-rep =  pi-cod-emitente NO-LOCK,
            EACH ped-venda
            WHERE nota-fiscal.nome-ab-cli = ped-venda.nome-abrev 
            and   nota-fiscal.nr-pedcli = ped-venda.nr-pedcli NO-LOCK
            BREAK BY nota-fiscal.cod-emitente :

            ACCUMULATE nota-fiscal.dt-emis-nota (MAXIMUM).
            ACCUMULATE nota-fiscal.nr-nota-fis (MAXIMUM).
            ACCUMULATE ped-venda.nr-pedcli (MAXIMUM).
            ACCUMULATE ped-venda.user-impl (MAXIMUM).
            
            IF LAST-OF(nota-fiscal.cod-emitente) THEN DO:
                CREATE tt-representante.
                ASSIGN
                tt-representante.cod-emitente = nota-fiscal.cod-emitente
                tt-representante.nome-ab-cli  = nota-fiscal.nome-ab-cli
                tt-representante.cod-rep      = repres.cod-rep
                tt-representante.nome         = repres.nome
                tt-representante.dt-emis-nota = (ACCUM MAXIMUM nota-fiscal.dt-emis-nota)  // max(nota_fiscal.dt_emis_nota) ult_dt,
                tt-representante.nr-nota-fis  = (ACCUM MAXIMUM nota-fiscal.nr-nota-fis)  // max(nota_fiscal.nr_nota_fis) ult_nota,
                tt-representante.nr-pedcli    = (ACCUM MAXIMUM ped-venda.nr-pedcli)  // max(ped_venda.u##nr_pedcli) nr_pedcli,
                tt-representante.user-impl    = (ACCUM MAXIMUM ped-venda.user-impl)  // max(upper(ped_venda.user_impl)) user_impl,
                .
            END.
    END.

cod-emitente is the ID of each company. So for each company, I want the last data they have stored in the system. As it is happening now, I'm getting the same result for each ID.

question from:https://stackoverflow.com/questions/65944880/how-can-i-get-the-last-element-of-multiple-items-in-progress

Answer 1

The accumulate and accum functions always trip me up so I tend to avoid them where possible. If you had avoided them too and just created the record on the first-of and used the maximum function on the rest, then you not have had a problem either.

if first-of( foo ) then do:
   create ttbar.
   assign
      ttbar.id    = foo.id
      ttbar.value = foo.value
      .
end.
else
   ttbar.value = maximum( ttbar.value, foo.value ).

If you really want to use accumulate then you will need to accumulate at the right level. Your maximum is now on everything, but you want it to be per company. So will need to use sub-maximum and indicate by what:

accumulate nota-fiscal.dt-emis-nota ( sub-maximum by nota-fiscal.cod-emitente )
...
tt-representante.dt-emis-nota = accum sub-maximum nota-fiscal.dt-emis-nota by nota-fiscal.cod-emitente

Here's an ABLdojo example showing the difference https://abldojo.services.progress.com/?shareId=6013b9f19585066c219797fa