CODEWITHSUNDEEP
sqlsql-server-2008openedgeprogress-4gl
Vegeta_77
Vegeta_77

Reputation: 464

ABL Progress 4gl : For Each with Count in Output-Stream

Progress-Procedure-Editor:

DEFINE STREAM myStream.

OUTPUT STREAM myStream TO 'C:\Temp\BelegAusgangSchnittstelle.txt'.

FOR EACH E_BelegAusgang
    WHERE E_BelegAusgang.Firma = '000'
    AND   E_BelegAusgang.Schnittstelle = '$Standard'
    NO-LOCK:

    PUT STREAM myStream UNFORMATTED
        STRING(E_BelegAusgang.Firma)
        '|'
        STRING(E_BelegAusgang.BelegNummer)
        '|'
        STRING(E_BelegAusgang.Schnittstelle)
        '|'
        SKIP
        .
END.

I get this (extraction):

Firma | BelegNr | Schnittstelle

000 | 3 | $Standard

000 | 3 | $Standard

000 | 3 | $Standard

000 | 3 | $Standard

000 | 3 | $Standard

000 | 8 | $Standard

000 | 8 | $Standard

What I need is to COUNT the BelegNr. So I import the data of the TXT to SQL Server.

On Server my query is:

SELECT  [BelegNr]
       ,COUNT(*) AS [Anzahl]
FROM [TestDB].[dbo].[Beleg_Ausgang]
GROUP BY [BelegNr]
ORDER BY [Anzahl]

With that query I got (extraction):

BelegNr Anzahl

3 | 5

8 | 2

Is there a way to put the COUNT directly into the Progress-Code? I mean, I want my result directly from the Progress-Procedure-Editor.

Upvotes: 1

Views: 789

Answers (1)

Jensd
Jensd

Reputation: 8011

In ABL you use BREAK BY instead of GROUP BY. One limit is that BREAK BY groups AND sorts.

You could for instance have another "FOR EACH" for this:

DEFINE VARIABLE iCount AS INTEGER     NO-UNDO.

FOR EACH E_BelegAusgang NO-LOCK 
    WHERE E_BelegAusgang.Firma = '000'
    AND   E_BelegAusgang.Schnittstelle = '$Standard'
    BREAK BY BelegNr:

    iCount = iCount + 1.

    IF LAST-OF(BelegNr) THEN DO:
        DISPLAY BelegNr iCount.
        iCount = 0.
    END.

END.

You could also incorporate that code in the export but note: that will change the order of the file rows. Maybe that's a problem for you, maybe not!

Upvotes: 3

Related Questions