Home > IT Answers > AS/400 > Cursor
22917
425 pts.
 Cursor
SQLRPGLE, AS 400
Hi, I have posted my SQLRPGLE program, output is not showing down,what mistake i did in my program,please correct it,

FMA01F     IF   E           K DISK   

FMA01FTES  IF A E    K DISK   RENAME(RMA01F:REMA01F) PREFIX(P_)   D COMNO           S              3  0       

D SHPNO           S              5  0  

D ITMNO           S             15A  

D TOTAL           S              7  2   

D COUNT           S              2  0      

C/EXEC SQL DECLARE cursor1 CURSOR FOR 

C+ SELECT MACMNO,SUM(MATOTA),Count(MACMNO) from MA01F         C+ Group By MACMNO 

C/END-EXEC   C/EXEC SQL OPEN cursor1    

C/END-EXEC           

C                   DoU       SQLSTT = '02000'     

C/EXEC SQL FETCH Cursor1 INTO :COMNO, :TOTAL, :COUNT          C/END-EXEC                

C                   If        SQLSTT = '02000'   

C                   Leave       

C                   EndIf                

C                   EVAL      P_MACMNO = COMNO    

C                   EVAL      P_MATOTA = TOTAL      

C                   EVAL      P_MACONT = COUNT     

C                   Write     REMA01F         

C                   EndDo      C/EXEC SQL CLOSE Cursor1             

C/END-EXEC                          C                  

EVAL      *InLr = *On   *****************End of Prog***********************

Original Result  hv to come as follows: COMPANY NUMBER   SUM ( MATOTA )   COUNT ( MACMNO )         1                                      848,963.17                53         2                                      316,476.85                21         3                                      418,042.80                20

 But I am getting as :

COMPANY NUMBER  TOTAL AMOUNT  COUNT         

0               .00      0         

0               .00      0         

0               .00      0



Software/Hardware used:
AS400
ASKED: Feb 1, 2012  7:26 AM GMT
UPDATED: March 17, 2012  5:19:37 AM GMT
RELATED QUESTIONS
Philpl1jb
36,410 pts.
 
  Help
 Approved Answer - Chosen by TomLiotta
the fetch fails because the fields aren't wide enough to accomodate the data but the program continues
You only test for SQLSTT = '02000' Normal end of file
You would want to make all three variables large enough to accept the largest totals and counts you could ever generate.
Phil
ANSWERED:  Feb 1, 2012  4:20 PM (GMT)  by Philpl1jb   36,410 pts.
 
Other Answers:
Hi, I have posted my SQLRPGLE program, output is not showing down,what mistake i did in my program,please correct it,
 
 FMA01F     IF   E           K DISK   
 
 FMA01FTES  IF A E    K DISK   RENAME(RMA01F:REMA01F) PREFIX(P_)   
 D COMNO           S              3  0       
 
 D SHPNO           S              5  0   
 
 D ITMNO           S             15A   
 
 D TOTAL           S              7  2    
 
 D COUNT           S              2  0       
 
 C/EXEC SQL DECLARE cursor1 CURSOR FOR  
 
 C+ SELECT MACMNO,SUM(MATOTA),Count(MACMNO) from MA01F         
 C+ Group By MACMNO  
 
 C/END-EXEC   C/EXEC SQL OPEN cursor1     
 
 C/END-EXEC            
 
 C                   DoU       SQLSTT = '02000'      
 
 C/EXEC SQL FETCH Cursor1 INTO :COMNO, :TOTAL, :COUNT          
 C/END-EXEC                 
 
 C                   If        SQLSTT = '02000'    
 
 C                   Leave        
 
 C                   EndIf                 
 
 C                   EVAL      P_MACMNO = COMNO     
 
 C                   EVAL      P_MATOTA = TOTAL       
 
 C                   EVAL      P_MACONT = COUNT      
 
 C                   Write     REMA01F          
 
 C                   EndDo     
 
 C/EXEC SQL CLOSE Cursor1              
 
 C/END-EXEC                          
 C                         EVAL      *InLr = *On  
 
 *****************End of Prog*********************** 
 
 Original Result  hv to come as follows: 
 
 COMPANY NUMBER   SUM ( MATOTA )   COUNT ( MACMNO )         1                                      848,963.17                53        
  2                                      316,476.85                21         
  3                                      418,042.80                20
 
  But I am getting as : 
 
 COMPANY NUMBER  TOTAL AMOUNT  COUNT          
 
 0               .00      0          
 
 0               .00      0         
 
 0               .00      0
Last Wiki Answer Submitted:  Feb 1, 2012  8:50 AM (GMT)  by  22917   425 pts.
To see other answers submitted to the Answer Wiki View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



 

Post it in a Code format. It will be better for understanding.

Pradeep.

Deepu9321
 3,355 pts.
 

Not sure but
TOTAL S 7 2
cannot receive number 316,476.85 , it would have to be min of 8 2

no impact but if MA01 is only used in SQL it don’t need an F spec
FMA01F IF E K DISK

Phil

Philpl1jb
 36,410 pts.

the fetch fails because the fields aren’t wide enough to accomodate the data but the program continues
You only test for SQLSTT = ‘02000′ Normal end of file
You would want to make all three variables large enough to accept the largest totals and counts you could ever generate.
Phil

Philpl1jb
 36,410 pts.
 

Thanks Phil
Its working …

22917
 425 pts.