• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1863
  • 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
 
MurpheyApplication ConsultantCommented:
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
 
MurpheyApplication ConsultantCommented:
debugging asset code?

Never heard of...
0
 
MurpheyApplication ConsultantCommented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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