CODEWITHSUNDEEP
progress-4glopenedge
FancyPanda
FancyPanda

Reputation: 85

GROUP BY Function in Progress OpenEdge

I am trying to find out how to do the Progress equivalent of a "GROUP BY" function. I am trying to write a procedure that will list product inventory information for multiple locations. Right now, it is listing the product information twice; once for each location. I have tried the BREAK BY functional unsuccessfully. My current & desired output and code is below:

Current Output:

current

Desired Output:

desired

DEF INPUT PARAMETER ip-um AS CHARACTER NO-UNDO.

MESSAGE
  "ProdCode" + "^" +
  "ProdName" + "^" +
  "ProdUM" + "^" +
  "GrossPkgdWeight" + "^" +
  "QtyOH - LOC1" + "^" +
  "QtyOH - LOC2"
  SKIP.

FOR EACH product-um WHERE
     product-um.gross-pkgd-weight <= 0.0000
     NO-LOCK,
EACH product WHERE
     product.product-key = product-um.product-key AND
     product.can-be-sold = YES
     NO-LOCK,
EACH inventory WHERE
     inventory.product-key = product.product-key AND
     inventory.qoh > 0 AND
     inventory.level = 2
     NO-LOCK,
EACH um WHERE
     um.um-key = product-um.um-key AND
     um.um = ip-um
     NO-LOCK
BREAK BY product.product-code:

MESSAGE
     product.product-code + "^" +
     product.product-name + "^" +
     um.um-code + "^" +
     STRING(product-um.gross-pkgd-weight) + "^" +
     IF inventory.level-key-2 = '00000001' THEN STRING(inventory.qoh) ELSE "0" 
     + "^" + IF inventory.level-key-2 = '00000002' THEN STRING(inventory.qoh) ELSE "0" 
     SKIP.
END.

Upvotes: 1

Views: 4871

Answers (2)

L.A
L.A

Reputation: 455

because you accumulate invesntory.qoh in dependency of inventory.level-key-2 the ACCUMULATE stmt is not realy feasible so coding the accumulation manually would be the best choise

DEFINE VARIABLE loc1 AS INTEGER NO-UNDO.
DEFINE VARIABLE loc2 AS INTEGER NO-UNDO.
FOR EACH  product-um NO-LOCK
    WHERE product-um.gross-pkgd-weight <= 0.0000
,
EACH  product NO-LOCK
WHERE product.product-key = product-um.product-key
  AND product.can-be-sold = YES
,
EACH  inventory NO-LOCK
WHERE inventory.product-key = product.product-key
  AND inventory.product-code = product.product-code
  AND inventory.qoh > 0
  AND inventory.level = 2
,
EACH  um NO-LOCK
WHERE um.um-key = product-um.um-key
  and um.um = ip-um
BREAK
   BY product.product-code:

  CASE (inventory.level-key-2):
    WHEN "00000001"
      THEN loc1 = loc1 + inventory.qoh.
    WHEN "00000002"
      THEN loc2 = loc2 + inventory.qoh.
   END CASE.
  IF LAST-OF(product.product-code)
  THEN DO:
    MESSAGE
      product.product-code + "^" +
      product.product-name + "^" +
      um.um-code + "^" +
      STRING(product-um.gross-pkgd-weight) + "^" +
      STRING(loc1) + "^" +
      STRING(loc2)
      SKIP.
    ASSIGN
      loc1 = 0
      loc2 = 0
    .
  END.
END.

Upvotes: 3

Tim Kuehn
Tim Kuehn

Reputation: 3251

BREAK BY tells the compiler to mark when the FOR EACH has come to the start or end of a break group.To detect these changes you'll need to use one of these functions to detect that change: FIRST(table.field), FIRST-OF(table.field), LAST(table.field), and LAST-OF(table.field).

Once the required function returns true, you can use the ABL supplied functions like ACCUMULATE, COUNT, TOTAL, etc. to display the desired results. Personally I find the concepts a bit hard to get my head around, so I declare some local variables and do the totals that way.

Upvotes: 1

Related Questions