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.

I don't see any "id" fields in your code so it is unclear to me what you mean by "the same result for each id". That sounds like there are many results but that some subset of the result fields is not changing? I do see that you are building tt-representante records with each LAST-OF. There is no definition shown for that TT but I would guess that cod-emitente is intended to be the unique key? Are you getting multiple records in that temp-table with multiple cod-emitente values? Or are you saying that you are only getting a single record in the temp-table? — Tom Bascom, Jan 28 '21 at 21:36
Instead of trying to hand off your work. Ask a question with an example that illustrates your problem. I doubt that your problem only occurs when you have four fields that need a maximum. — Stefan Drissen, Jan 29 '21 at 07:17
The cod-emitente is the id field. And sorry if it seems I'm trying to hand off my work. I'm trying to understand better how to utilize accum maximum and for this example and future uses and used my work as a way of doing it. If I only asked here for examples of using the ACCUMULATE without providing a code, I think people would've complained about it. — Diego, Jan 29 '21 at 10:38
You can remove at least half of the code in your example while still illustrating your problem. — Stefan Drissen, Jan 29 '21 at 12:02
So your actual question is about the use of ACCUMULATE? And you don't really have a problem with "getting the last element"? — Tom Bascom, Jan 29 '21 at 16:28
I wanted to use the ACCUMULATE to get the last element the same way I would use in an SQL query. I managed to do it without the ACCUMULATE. I guess it's better to avoid it as I hardly ever make it work ok. Sorry for wasting your time. — Diego, Jan 29 '21 at 16:50

score 2 · Accepted Answer · answered Jan 29 '21 at 07:32

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

I 100% agree that it is best to avoid trying to use ACCUMULATE and company. Those functions always lead to confusion. — Tom Bascom, Jan 29 '21 at 16:30

score 0 · Answer 2 · answered Jan 29 '21 at 14:25

I managed to do it by removing the ACCUMULATEs and using BY in the other elements. With the LAST-OF() function I got the last element from all of them, as if I had used the SQL max() function. It's a shame I couldn't use the ACCUMULATE to learn how to use it better. Thanks for the suggestion @Stefan and sorry if it seemed I was lazily passing my work onto you. When I get stuck in a problem, I post my code here and then proceed to change the code, trying as many different things as possible. I prefer to stay stuck in a code for days and completing it than to change codes and not being able to complete stuff.

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

2 Answers2