Progress ABL: BREAK BY With multiple joins in an OPEN QUERY

Question

I'm pretty new to ABL (and I'm French so please excuse my English) so I need your help. I'd like to know how to use a BREAK BY in an OPEN QUERY.

Here's what I do:

define query Q-REQ for 
ENT_RCP_FRN,
LIG_RCP_FRN,
CONSO_UNV_MDIM,
LIG_DOC_TRS,
ENT_DOC_TRS scrolling.

open query Q-REQ for
 each ENT_RCP_FRN no-lock
   where  ENT_RCP_FRN.STO-c-CodeDes = "DRET", 
 each LIG_RCP_FRN no-lock break by LIG_RCP_FRN.SKU-c-cod
   where LIG_RCP_FRN.SOU-c-Cod = ENT_RCP_FRN.SOU-c-Cod 
   and LIG_RCP_FRN.ERF-c-NumRcpFrn = ENT_RCP_FRN.ERF-c-NumRcpFrn,
 each CONSO_UNV_MDIM no-lock
  where CONSO_UNV_MDIM.UMA-c-Code = "8B6A9/0001354"
  and CUMD-c-LstCleCumConso = LIG_RCP_FRN.SKU-c-cod,
 each LIG_DOC_TRS no-lock LEFT OUTER-JOIN
   where LIG_DOC_TRS.SKU-c-Cod = LIG_RCP_FRN.SKU-c-Cod,
 each ENT_DOC_TRS no-lock left outer-join
   where ENT_DOC_TRS.SOU-c-Cod = LIG_DOC_TRS.SOU-c-Cod
   and ENT_DOC_TRS.EDT-c-NumDocTrs = LIG_DOC_TRS.EDT-c-NumDocTrs
   and ENT_DOC_TRS.TR-c-CodeCatDoc = "BT"
   and ENT_DOC_TRS.STO-c-CodeOri = "DRET"
   and ENT_DOC_TRS.STO-c-CodeDes = "DWHO".


repeat :

     get next Q-REQ.
     if not available ENT_RCP_FRN then leave.


end.

I get an error at the following statement:

each LIG_RCP_FRN no-lock break by LIG_RCP_FRN.SKU-c-cod
   where LIG_RCP_FRN.SOU-c-Cod = ENT_RCP_FRN.SOU-c-Cod 
   and LIG_RCP_FRN.ERF-c-NumRcpFrn = ENT_RCP_FRN.ERF-c-NumRcpFrn,

Unable to understand after: "LIG_RCP_FRN.SKU-c-cod" (my error is in French, but it is error 247):

247** L'expression après : "LIG_RCP_FRN.SKU-c-cod" est incompréhensible. (247)

What I'd like to do is to be able to sum a column from the table LIG_RCP_FRN, breaking when the SKU-c-Cod changes.

I tried to delete every statement after this error, but It didn't work neither.

Please ask if you need more information. Openedge progress 10.2A

Thank you for your help, Corentin

PS: impossible to say "Hi" in a post??

According to the manual (albeit v9.1) - you can order a query using "BY" in an opened query, but you cannot use break by. You need to use a FOR EACH loop if you want to use "BREAK BY". — Screwtape, Apr 26 '16 at 10:35
@Screwtape That is not correct, at least not in later versions. — Jensd, Apr 26 '16 at 10:55
That's good then. I can't test beyond v9 at the moment. In a FOR EACH the manual implies that the BREAK BY should be after all the record phrases, so perhaps just moving it to the end of the query will work in OE10? Just a guess. — Screwtape, Apr 26 '16 at 10:58

Jensd · Answer 1 · 2016-04-26T11:40:59.707

It's a very simple mistake. You need to put the "BREAK BY" option in the very end of the query.

Also it's very nice if you post some (could be fake) temp-table definition that makes it easy to copy-and-paste.

I improvised some tables in the code below based on the query, they are only there to make the code compile.

The FIRST-OF method called on the query works like this:

query:FIRST-OF(level)

index: An integer expression that indicates a BREAK BY group, where 0 is the entire query, 1 is the first BREAK BY, 2 is the second BREAK BY, and so on.

If you have BREAK BY fieldA BY fieldB then

query:FIRST-OF(1) relates to BREAK BY fieldA and

query:FIRST-OF(2) relates to BREAK BY fieldB

There's also LAST-OF(n)

/* Completely fake temp-tables that are here only to make the query work! */
DEFINE TEMP-TABLE ENT_RCP_FRN NO-UNDO
    FIELD STO-c-CodeDes   AS CHARACTER
    FIELD SOU-c-Cod       AS INTEGER
    FIELD ERF-c-NumRcpFrn AS INTEGER.

DEFINE TEMP-TABLE LIG_RCP_FRN NO-UNDO
    FIELD SOU-c-Cod       AS INTEGER
    FIELD ERF-c-NumRcpFrn AS INTEGER
    FIELD SKU-c-cod       AS INTEGER.

DEFINE TEMP-TABLE CONSO_UNV_MDIM NO-UNDO
    FIELD UMA-c-Code            AS CHARACTER
    FIELD CUMD-c-LstCleCumConso AS INTEGER.

DEFINE TEMP-TABLE LIG_DOC_TRS NO-UNDO
    FIELD SKU-c-Cod       AS INTEGER
    FIELD SOU-c-Cod       AS INTEGER
    FIELD EDT-c-NumDocTrs AS INTEGER.

DEFINE TEMP-TABLE ENT_DOC_TRS NO-UNDO
    FIELD SOU-c-Cod       AS INTEGER
    FIELD EDT-c-NumDocTrs AS INTEGER
    FIELD TR-c-CodeCatDoc AS CHARACTER
    FIELD STO-c-CodeOri   AS CHARACTER
    FIELD STO-c-CodeDes   AS CHARACTER.


DEFINE QUERY Q-REQ FOR ENT_RCP_FRN, LIG_RCP_FRN, CONSO_UNV_MDIM, LIG_DOC_TRS, ENT_DOC_TRS  SCROLLING.

OPEN QUERY Q-REQ FOR EACH ENT_RCP_FRN NO-LOCK WHERE ENT_RCP_FRN.STO-c-CodeDes = "DRET",
    EACH LIG_RCP_FRN NO-LOCK WHERE LIG_RCP_FRN.SOU-c-Cod = ENT_RCP_FRN.SOU-c-Cod 
                               AND LIG_RCP_FRN.ERF-c-NumRcpFrn = ENT_RCP_FRN.ERF-c-NumRcpFrn,
    EACH CONSO_UNV_MDIM NO-LOCK WHERE CONSO_UNV_MDIM.UMA-c-Code = "8B6A9/0001354"
                                 AND CUMD-c-LstCleCumConso = LIG_RCP_FRN.SKU-c-cod,
    EACH LIG_DOC_TRS NO-LOCK LEFT OUTER-JOIN WHERE LIG_DOC_TRS.SKU-c-Cod = LIG_RCP_FRN.SKU-c-Cod,
    EACH ENT_DOC_TRS NO-LOCK LEFT OUTER-JOIN WHERE ENT_DOC_TRS.SOU-c-Cod = LIG_DOC_TRS.SOU-c-Cod
                                               AND ENT_DOC_TRS.EDT-c-NumDocTrs = LIG_DOC_TRS.EDT-c-NumDocTrs
                                               AND ENT_DOC_TRS.TR-c-CodeCatDoc = "BT"
                                               AND ENT_DOC_TRS.STO-c-CodeOri = "DRET"
                                               AND ENT_DOC_TRS.STO-c-CodeDes = "DWHO" BREAK BY LIG_RCP_FRN.SKU-c-cod.


REPEAT:

     GET NEXT Q-REQ.
     IF NOT AVAILABLE ENT_RCP_FRN THEN LEAVE.

     IF QUERY q-req:FIRST-OF(1) THEN DO:
         DISPLAY "First!".
     END.

END.

Hi, Thank you for your answer. I'm running some tests. I don'tn encounter errors anymore, but the result is not what I'm expecting. I'll keep you informed. — Corentin, Apr 27 '16 at 10:38
@Corentin If you need more help you need to describe what you except and post more code! — Jensd, Apr 27 '16 at 10:39

Progress ABL: BREAK BY With multiple joins in an OPEN QUERY

1 Answers1