SQL vs. RPG
Posted on 2001-06-22
I created an SQL cursor on my item master(IM), opened and fetched the first record. Now I want to process that record in my inventory transaction file(ITH).
I create a second cursor where the item in my ITH is equal to the item read in my IM, and I also retrieve other fields as well, specifically the date of the transaction and the qty.
Now I want to process the ITH record(s) for the item retrieved in the first cursor. A field (TREF), contains the shop order number. This field now acts as a break. When the next record read has a different value in TREF then the previous record, I want to break, sum the qty of all the prior records with the same TREF value. Then I want to write to a PRTF, the item, summed qty of TQTY and the date of the last record read with the original TREF value.
As a note the ITH is ordered by item, shop order, date.
I have finished the report and it works, but I read each record (primary and secondary) using standard fetch statements. Is there a way to do all of this in a cursor, or do you recommend what I did, declare the cursor, fetch, process, fetch, EOF, close cursor.
If you agree with my way of doing this, I will give the 50 points, however if there is a way to create the second cursor and have the TQTY summed and the date of the last record available for a write to the PRTF, I will increase the points for the code. My code original code is below:
C+ DECLARE IIMC CURSOR FOR SELECT IPROD, IDESC, IITYP FROM IIML01 WHERE
C+ IPACK = 0 AND IITYP IN('F', 'M') AND IPROD = (SELECT ICPROD FROM
C+ CIC WHERE IPROD = ICPROD AND ICPLN = :WTWHS AND ICCLAS = :WTCLAS)
C+ OPEN IIMC
C+ FETCH NEXT FROM IIMC INTO :WIPROD, :WIDESC, :WIITYP
C DOW SQLCOD=0
C+ DECLARE ITHC CURSOR FOR SELECT TPROD, TREF, TTDTE, TQTY FROM ITHL91
C+ WHERE TPROD = :WIPROD AND TTYPE = 'R' AND TCLAS = :WTCLAS
C+ AND TWHS = :WTWHS ORDER BY TPROD, TREF, TTDTE
C+ OPEN ITHC C DOW SQLCOD=0
C+ FETCH NEXT FROM ITHC INTO :WTPROD, :WTREF, :WTTDTE, :WTQTY
C Z-ADD *ZEROS WTREF1 6 0
C Z-ADD *ZEROS SUMTQTY
C EVAL WTREF1 = WTREF
C EVAL WTTDTE1 = WTTDTE
C DOW SQLCOD=0
Process records, fetch, etc.....