Solved

SQL vs. RPG

Posted on 2001-06-22
5
1,828 Views
Last Modified: 2012-05-04
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
Comment
Question by:jjjjjjj
  • 3
  • 2
5 Comments
 
LVL 16

Accepted Solution

by:
theo kouwenhoven earned 50 total points
ID: 6223680
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
 
LVL 1

Author Comment

by:jjjjjjj
ID: 6224810
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
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 6225146
debugging asset code?

Never heard of...
0
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 6230636
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
 
LVL 1

Author Comment

by:jjjjjjj
ID: 6232748
Thanks for the info.

jjjjjjj
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Is your phone running out of space to hold pictures?  This article will show you quick tips on how to solve this problem.
ConnectWise and their customers need to ensure critical alerts automatically reach the right person at the right time. MSP superheros efficiently respond to these alerts key is providing automatic, intelligent alerting that generates a complete audi…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question