Reading through multiple records, with Loop capability
--------------------------------------------------------------------------------
Hi,
I have three tables as below:
Table: Demand
ITEM_ID, QUANTITY
Item A, 10
Table: Route
ITEM_ID, OPERATION_NO, RESOURCE, TIME
Item A, 10, RES1, 60
Item A, 20, RES2, 120
Item A, 30, RES3, 60
Table: Load
RESOURCE, LOAD
RES1, 600
RES2, 1200
RES3 , 600
The values in LOAD field of the LOAD table are calculated values.
What I want to achieve?
I want to calculate the LOAD field in the LOAD table
Steps:
1) Select the records for every ITEM_ID (e.g. Item A ) in the Route table.
2) Start with the lowest number of Operation_No (e.g. Operation_No 10), check the following:
a. Value in the RESOURCE field (e.g. Res1) and value in the TIME field (e.g. 60).
3) Multiply the value in the ROUTE.TIME field with the corresponding value in DEMAND.QUANTITY. (ROUTE.ITEM_ID = DEMAND.ITEM_ID)
4) Update the Table Load with the following values:
a. RES1 in field RESOURCE
b. The multiplied product we got in Step 3 above in the LOAD.LOAD field.
5) Go back to Step 2 above and select the next lowest record for Item A ( Operation_No = 20 in this case) and perform the following steps:
a. Step 2.a
b. Step 3
c. Step 4
6) Go back to Step 2. Continue this iteration (loop) until we complete all the records for this item. (In this case, there are three records for Item A: Operation_No: 10, 20 & 30).
7) Once all the records for Item A are processed and the LOAD table is updated, then go back to Step 1 and select the next item.