[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1860
  • Last Modified:

SQL vs. RPG

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/END-EXEC                                                                
                                                                           
 C/EXEC SQL                                                                
 C+ OPEN IIMC                                                              
 C/END-EXEC                                                                
                                                                           
 C/EXEC SQL                                                                
 C+ FETCH NEXT FROM IIMC INTO :WIPROD, :WIDESC, :WIITYP                    
 C/END-EXEC                                                                
                                                                           
                                                                           
 C                   DOW       SQLCOD=0                                    
                                                                           
 C/EXEC SQL                                                                
 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/END-EXEC                                                                
                                                                           
 C/EXEC SQL                                                                
 C+ OPEN ITHC                                                       C                   DOW       SQLCOD=0                                
                                                                       
 
                                                                       
 C/EXEC SQL                                                            
 C+ FETCH NEXT FROM ITHC INTO :WTPROD, :WTREF, :WTTDTE, :WTQTY          
 C/END-EXEC                                                            
                                                                       
 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.....


Thanks,

jjjjjjj                                                                  
0
jjjjjjj
Asked:
jjjjjjj
  • 3
  • 2
1 Solution
 
theo kouwenhovenCommented:
So, You are working with BPCS.....

The SQL solution is a great option, but not always the most practical one.
Of cause u can use SQL; but maybe you can solve this problem better in an OPNQRYF (open query file) solution.
I did both ways and it's hard to messure the result.
The last program I build with this kind of solutions was an interactive program with an OPNQRYF, where the select statment was build depending on the input from the first program and the result was displayed in the second program.
The for first user that start the program an accesspath is build this takes about 16 seconds. After that every selection takes less than 5 seconds with an Item Master of 98000 records and an ITH of 16.000.000 records.
The OPNQRYF is selecting the Item Master and the ITH is readed in the program.
It looks like this:

PGM PARM(&QRYSLT &RTNCD)                                
                                                       
DCL VAR(&QRYSLT) TYPE(*CHAR) LEN(512)                  
DCL VAR(&RTNCD)  TYPE(*CHAR) LEN(1)                    
                                                       
        OVRDBF     FILE(IIML12) TOFILE(IIM) SHARE(*YES)
                                                       
        OPNQRYF    FILE((IIML12)) QRYSLT(&QRYSLT) +    
                     KEYFLD((IDESC) (IPROD))            
                                                       
        CALL       PGM(UR3325C) PARM(&RTNCD)            
                                                       
CLOF OPNID(IIML12)                                      
DLTOVR FILE(IIML12)                                    
                                                       
ENDPGM                              

Good luck.
                   
0
 
jjjjjjjAuthor Commented:
Yep,

I'm working with BPCS.  Are you as well?

I will look at the code you provided and will advise.  By the way, how do you like debugging asset code?

jjjjjjj
0
 
theo kouwenhovenCommented:
debugging asset code?

Never heard of...
0
 
theo kouwenhovenCommented:
We have  build our own "Project-management" module with a financial interface that handel all the asset stuf.
So you have to ask someone else.

Regards.
0
 
jjjjjjjAuthor Commented:
Thanks for the info.

jjjjjjj
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now