Solved

SQL vs. RPG

Posted on 2001-06-22
5
1,843 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 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows how to use a free utility called 'Parkdale' to easily test the performance and benchmark any Hard Drive(s) installed in your computer. We also look at RAM Disks and their speed comparisons.
This article outlines some of the reasons why an email message gets flagged as spam on a recipient's end.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

688 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