Using Break By for Multiple Fields

Question

I would like to ask how to use multiple break by in a for each statement.

Sample:

Car Code   Color Code
0001       002
0001       002
0001       001
0005       003
0005       002
0007       001
0008       001
0008       005
0008       001

My code is:

def var ctr as int.

For each car no-lock break by carcode by colorcode.

   ctr = ctr + 1.


/*I tried*/
   if last-of(carcode) and last-of(colorcode) then do:
      disp carcode colorcode ctr.
      ctr = 0.
   end. 


/*and tried*/
   last-of(colorcode) then do:
      if last-of(carcode) 
         disp carcode colorcode ctr.
         ctr = 0.
      end.
   end. 
end.

My expected output would be:

car code   Color Code    QTY
0001       001           1
0001       002           2
0005       002           1
0005       003           1
0007       001           1
0008       001           2
0008       005           1

What language are you talking about? SQL? Also, the question is not very clear. Do you want to count the rows with the same Car Code and Color Code? — Fabio says Reinstate Monica, Sep 26 '15 at 01:30
i am using progress 4gl / openedge sorry for not clarifying. What i want is to display the total quantity of the car code with its corresponding color.. — noob, Sep 26 '15 at 01:42

score 3 · Answer 1 · answered Sep 26 '15 at 03:22

3

Try this:

FOR EACH tablename NO-LOCK 
   BREAK BY carcode 
         BY colorcode: 

   ctr = ctr + 1.

   if last-of(carcode) OR last-of(colorcode) then do:
      disp carcode colorcode ctr.
      ctr = 0.
   end. 
END.

It is possible for LAST-OF(colorcode) to be true and LAST-OF(carcode) be false, so change the AND to an OR.

If LAST-OF(carcode) is true, then LAST-OF(colorcode) will also be true.

answered Sep 26 '15 at 03:22

Tim Kuehn

3,201
1
17
23

I tried your suggestion but it did not work. it had a different output though. – noob Sep 26 '15 at 05:53

noob · Answer 2 · 2015-09-29T07:48:38.313

as I was checking on the code, i disregard the use of the last-of and used a temp-table and buffer instead.

def buffer btt-car for tt-car.

find first tt-car where tt-car.carcode = car.carcode exclusive.
if not avail tt-car then do:
  create tt-car.
  assign tt-car.car-code = car.carcode
  tt-car.color-code = car.colorcode
  tt-car.qty = tt-car.qty + car.qty.
end.
if avail tt-car then do:
  find first btt-car where btt-car.colorcode = car.colorcode exclusive.
  if not avail btt-car then do:
    create btt-car.
    assign btt-car.car-code = car.carcode
    btt-car.color-code = car.colorcode
    btt-car.qty = btt-car.qty + car.qty.
  end.
  if avail btt-car then assign btt-car.qty = btt-car.qty + car.qty.
end.

but if you guys have solutions with using last-of from break by, please share..

thanks

score 0 · Answer 3 · answered Sep 28 '15 at 07:47

Something like this should work:

for each car 
    no-lock
    break by car.carcode
          by car.colorcode
:

    accumulate car.colorcode (count by car.colorcode).

    if last-of(car.colorcode)
    then
        display car.carcode 
                car.colorcode
                (accum count by car.colorcode car.colorcode).

end.

You can use a variable instead of ACCUMULATE if you want, of course.

Using Break By for Multiple Fields

3 Answers3