?
Solved

SQL vs. RPG

Posted on 2001-06-22
5
Medium Priority
?
1,848 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 16

Accepted Solution

by:
theo kouwenhoven earned 200 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

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.

Question has a verified solution.

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

New style of hardware planning for Microsoft Exchange server.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month10 days, 15 hours left to enroll

770 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