I am stumped at the moment. What I have to do is, when the main loop is reading an order which is aaord#, we need to tally in this subroutine all the rest of the order rows which has a box weight. the shipping table is like this: for the output, we can put the ord_wt on each row, I can't think of any other way.
PHORD# PHWGHT PHBNO#
04924920 1.05 1
05012409 27.40 2
05012409 27.40 3
05012409 27.40 4
05012409 27.40 5
05012409 27.40 6
05012409 27.40 7
05012409 27.40 8
05012409 20.00 9
05012421 26.90 2
05012421 26.90 3
05012421 26.90 4
05012430 13.70 2
05036997 21.60 1
05036997 21.60 2
05036997 21.60 3
05036997 21.60 4
05037155 14.55 1
05037173 12.25 1
05037173 12.20 2
05039479 8.10 1
so in this code, what I want to do is see if the order number is not = to previous order number, then I will do this code to tally up all the order weights from the ship table. I also need to clear the holding field when there is such a new order number.\ but my output is only zeros in the ord_wt
c eval mhcmno4= aacom#
c* eval wkrel@ = %EDITC(aarel#:'X')
c* eval wkrel2 = %subst(wkrel@:4:2)
c eval mhordr4 = aaord#
c eval wkvsf='N'
c* endif
c z-add 0 phwtno 702
c*
c mhordr4 ifne prvord
c z-add 0 phwtot
c mhkey4 setll pshipLL4
c read pshipLL4
c* loop thru all orders in the ship table and add the weight to get a
c* total weight per order #
c dow not %eof(pshipLL4)
c if mhcmno4 = PHCOM# and
c mhordr4 = PHORD#
c* phwght is 11 char
c**
c eval prvord = mhordr4
c eval phwtno = %dec(PHWGHT:7:2)
c add phwtno phwtot
c else
c leave
c endif
c read pshipLL4
c enddo
c endif
c endsr
OUTPUT: packages of course cannot be 0.
05475731 0
05475731 0
05475731 0
05475731 0
05475731 0
05475731 0
05475731 0
05475731 0
05475731 0
05476179 0
05476179 0
05476179 0
05476179 0
05476179 0
05476179 0
05476179 0
05476179 0
05476179 0
05476179 0
05476179 0
05476179 0
05476179 0
05476179 0
05476179 0
05476179 0
05475736 0
05475736 0
05475736 0
05475736 0
05475736 0
05475736 0
05475736 0
05475736 0
05475736 0
05475736 0
05475750 0
05475750 0
05475750 0
05475750 0
05475750 0
05475750 0
you could do this with SQL something like this:
exec sql
with tmp as (
select orderno, sum(weight) as orderweight
from orderdetail
group by orderno)
select orderno, itemno, weight, orderweight
into :localdatastructure
from orderdetail
join tmp using(orderno)
where orderno = :localvariable;
where local variable is the order number you are processing, and local datastructure is a data structure defined to fit your output record. You need to make sure you are only reading a single record at a time, or you can put this into a cursor to read multiple records.