Distinct or group by in Epicor 9 BAQ editor

I need to join the TranGLC table to the APInvDtl table in Epicor 905 in a Business Activity Query.

I am currently joining on Company, VendorNum, InvoiceNum, InvoiceLine, JobNum and can't see any other columns that would be suitable for the join.

My problem is that join does not uniquely identify the records in the APInvDtl table, so if my query returns 14 rows from TranGLC, when I join the two tables, I get 196 records.

In sql I can use the Distinct key word, and I get the results I'm looking for, but I can't see a way to do that in the BAQ editor.

I can also get the results I"m looking for by using a SQL group by, but again, I don't know how to do that in the BAQ editor.

I've researched ABL, and it looks like you can do a first-of in ABL code that might behave in the same way group by does, but I can't see how to do that in the BAQ editor either.

Any suggestions on how to remove the duplicates from the result set would be greatly appreciated.

Without your code its hard to know where to start.

Generally joining is done like this:

FOR EACH table1 NO-LOCK, [EACH|FIRST|LAST] table2 WHERE table1.id = table2.id:
END.

Based on you info you have 2 EACHs - meaning you iterate all records in both tables. Without more info it's hard to move one. Perhaps you need to do:

FOR EACH table1 NO-LOCK, FIRST table2 NO-LOCK WHERE [...]

Or as you say a BREAK BY. That would be something like:

FOR EACH table1 NO-LOCK, 
    EACH table2 NO-LOCK WHERE [...] BREAK BY table1.id1 BY table2.id2:

    IF FIRST-OF(table1.id1) THEN DO:
      /* Something */
    END.

    IF LAST-OF(table1.id1) THEN DO:
      /* Something */
    END.
END.

You can do FIRST(table1.id1) and LAST(table1.id1) as well- that returns true on the "globally" first and last group instead of for every group.

/* Long example */
DEFINE TEMP-TABLE ttTest NO-UNDO
    FIELD a AS CHARACTER FORMAT "X" LABEL "A"
    FIELD b AS CHARACTER FORMAT "X" LABEL "B".


CREATE ttTest.
ASSIGN ttTest.a = "a"
       ttTest.b = "b".

CREATE ttTest.
ASSIGN ttTest.a = "a"
       ttTest.b = "b".

CREATE ttTest.
ASSIGN ttTest.a = "a"
       ttTest.b = "b".

CREATE ttTest.
ASSIGN ttTest.a = "a"
       ttTest.b = "b".

CREATE ttTest.
ASSIGN ttTest.a = "b"
       ttTest.b = "b".

CREATE ttTest.
ASSIGN ttTest.a = "b"
       ttTest.b = "b".


CREATE ttTest.
ASSIGN ttTest.a = "b"
       ttTest.b = "c".

CREATE ttTest.
ASSIGN ttTest.a = "c"
       ttTest.b = "c".

FOR EACH ttTest NO-LOCK BREAK BY ttTest.a BY ttTest.b:

    DISPLAY ttTest.

    IF FIRST(ttTest.a) THEN DISPLAY "X" COLUMN-LABEL "F(a)".

    IF FIRST(ttTest.b) THEN DISPLAY "X" COLUMN-LABEL "F(b)".

    IF FIRST-OF(ttTest.a) THEN DISPLAY "X" COLUMN-LABEL "F-O(a)".

    IF FIRST-OF(ttTest.b) THEN DISPLAY "X" COLUMN-LABEL "F-O(b)".

    IF LAST-OF(ttTest.a) THEN DISPLAY "X" COLUMN-LABEL "L-O(ta)".

    IF LAST-OF(ttTest.b) THEN DISPLAY "X" COLUMN-LABEL "L-O(b)".

    IF LAST(ttTest.a) THEN DISPLAY "X" COLUMN-LABEL "L(a)".

    IF LAST(ttTest.b) THEN DISPLAY "X" COLUMN-LABEL "L(b)".

END.

Will result in: