CODEWITHSUNDEEP
openedgeprogress-4gl
Diego
Diego

Reputation: 57

How can I use last-of in a QUERY-PREPARE()?

I made a huge code and it was taking a very long time to process. Following suggestions here, I made it shorter and better and now it's running much faster. However, I noticed I should be getting a sum of the values whereas I'm just getting the values. I tried using ACCUMULATE TOTAL, but it didn't work because the LAST-OF can only be used with a BREAK BY and my break by is inside the QUERY-PREPARE().

How can I get the sum of the following values?

doc-fiscal.vl-cont-doc
doc-fiscal.vl-bicms
doc-fiscal.vl-icms
doc-fiscal.vl-icmsou
doc-fiscal.vl-ipiou
doc-fiscal.vl-ipi

Bellow is the code I'm using, which is working almost perfectly.

//Query
cQuery = "FOR EACH doc-fiscal WHERE doc-fiscal.dt-docto >= " + QUOTER(pd-data-1) + " AND doc-fiscal.dt-docto <= " + QUOTER(pd-data-2) + " AND (doc-fiscal.cod-observa <> 4 OR doc-fiscal.tipo-nat <> 3) AND doc-fiscal.cd-situacao <> 06 AND doc-fiscal.cd-situacao <> 22".
cQuery = cQuery + semCodEmitente + comCodEmitente + CheckBoxindSitDoc.
cQuery = cQuery + ", EACH natur-oper USE-INDEX natureza WHERE doc-fiscal.nat-operacao = natur-oper.nat-operacao" + modeloEletronico + tipoEntrada + natOper.
cQuery = cQuery + " BREAK BY doc-fiscal.dt-docto BY doc-fiscal.nr-doc-fis ".

QUERY qRelatorio:QUERY-PREPARE(cQuery).

QUERY qRelatorio:QUERY-OPEN().

GET FIRST qRelatorio.
DEF VAR soma-vl-cont-doc AS DECIMAL.
DO WHILE AVAILABLE doc-fiscal:
    
    soma-vl-cont-doc = soma-vl-cont-doc + doc-fiscal.vl-cont-doc.

    IF LAST-OF(doc-fiscal.nr-doc-fis) THEN DO:
    CREATE tt-relatorio.
    ASSIGN
    tt-relatorio.nr-doc-fis = doc-fiscal.nr-doc-fis
    tt-relatorio.serie = doc-fiscal.serie
    tt-relatorio.char-2 = SUBSTRING(doc-fiscal.char-2,155,44, "CHAR")
    tt-relatorio.cod-model-nf-eletro = natur-oper.cod-model-nf-eletro
    tt-relatorio.tipo = natur-oper.tipo
    tt-relatorio.cod-estabel = doc-fiscal.cod-estabel
    tt-relatorio.cod-emitente = doc-fiscal.cod-emitente
    tt-relatorio.nome-ab-emi = doc-fiscal.nome-ab-emi
    tt-relatorio.cgc = doc-fiscal.cgc
    tt-relatorio.dt-emis-doc = doc-fiscal.dt-emis-doc
    tt-relatorio.dt-docto = doc-fiscal.dt-docto
    tt-relatorio.ind-sit-doc = doc-fiscal.ind-sit-doc
    tt-relatorio.vl-cont-doc = doc-fiscal.vl-cont-doc
    tt-relatorio.vl-bicms = doc-fiscal.vl-bicms
    tt-relatorio.vl-icms = doc-fiscal.vl-icms
    tt-relatorio.vl-icmsou = doc-fiscal.vl-icmsou
    tt-relatorio.vl-ipiou = doc-fiscal.vl-ipiou
    tt-relatorio.vl-ipi = doc-fiscal.vl-ipi
    tt-relatorio.imp-nota = natur-oper.imp-nota.
    
    GET NEXT qRelatorio.
    END.
END.

QUERY qRelatorio:QUERY-CLOSE().

Thanks for the help and sorry for being such a newbie. I hope my question can help other people.

Upvotes: 0

Views: 72

Answers (1)

Diego
Diego

Reputation: 57

I managed to do it. I used LAST-OF() METHOD. My code became the following.

DEF VAR soma-vl-cont-doc AS DECIMAL. //INICIO DAS SOMAS
DEF VAR soma-vl-bicms AS DECIMAL.
DEF VAR soma-vl-icms AS DECIMAL.
DEF VAR soma-vl-icmsou AS DECIMAL.
DEF VAR soma-vl-ipiou AS DECIMAL.
DEF VAR soma-vl-ipi AS DECIMAL.

DO WHILE AVAILABLE doc-fiscal:
    
    soma-vl-cont-doc = soma-vl-cont-doc + doc-fiscal.vl-cont-doc.
    soma-vl-bicms = soma-vl-bicms + doc-fiscal.vl-bicms.
    soma-vl-icms = soma-vl-icms + doc-fiscal.vl-icms.
    soma-vl-icmsou = soma-vl-icmsou + doc-fiscal.vl-icmsou.
    soma-vl-ipiou = soma-vl-ipiou + doc-fiscal.vl-ipiou.
    soma-vl-ipi = soma-vl-ipi + doc-fiscal.vl-ipi.
   
    IF QUERY qRelatorio:LAST-OF(2) THEN DO:

    CREATE tt-relatorio.
    ASSIGN
    tt-relatorio.nr-doc-fis = doc-fiscal.nr-doc-fis
    tt-relatorio.serie = doc-fiscal.serie
    tt-relatorio.char-2 = SUBSTRING(doc-fiscal.char-2,155,44, "CHAR")
    tt-relatorio.cod-model-nf-eletro = natur-oper.cod-model-nf-eletro
    tt-relatorio.tipo = natur-oper.tipo
    tt-relatorio.cod-estabel = doc-fiscal.cod-estabel
    tt-relatorio.cod-emitente = doc-fiscal.cod-emitente
    tt-relatorio.nome-ab-emi = doc-fiscal.nome-ab-emi
    tt-relatorio.cgc = doc-fiscal.cgc
    tt-relatorio.dt-emis-doc = doc-fiscal.dt-emis-doc
    tt-relatorio.dt-docto = doc-fiscal.dt-docto
    tt-relatorio.ind-sit-doc = doc-fiscal.ind-sit-doc
    tt-relatorio.vl-cont-doc = soma-vl-cont-doc
    tt-relatorio.vl-bicms = soma-vl-bicms
    tt-relatorio.vl-icms = soma-vl-icms
    tt-relatorio.vl-icmsou = soma-vl-icmsou
    tt-relatorio.vl-ipiou = soma-vl-ipiou
    tt-relatorio.vl-ipi = soma-vl-ipi
    //tt-relatorio.idi-sit-nf-eletro = nota-fiscal.idi-sit-nf-eletro
    tt-relatorio.imp-nota = natur-oper.imp-nota.
    soma-vl-cont-doc = 0.
    soma-vl-bicms = 0.
    soma-vl-icms = 0.
    soma-vl-icmsou = 0.
    soma-vl-ipiou = 0.
    soma-vl-ipi = 0.
    END.
    GET NEXT qRelatorio.
END.

With this, I managed to get the answers I wanted andthe query is quite fast. If there's any suggestions as to how make it faster, I'm open for them. Thanks.

Upvotes: 1

Related Questions