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.
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