Ü   Embedded SQL using SQLSTMT without cursor

 

 

 

Physical file used in the program = RESULT1

 


Columns . . . :    1  80                    Browse                           AMIT/QRPGLESRC
SEU==>                                                                              RESULT1
FMT PF .....A..........T.Name++++++RLen++TDpB......Functions+++++++++++++++++++++++++++
*************** Beginning of data *********************************************************
0001.00      A          R RES0001                                                           
0002.00      A            PARTY_NO      12A                                                 
0003.00      A            COUNT          2P 0                                                
0004.00      A          K PARTY_NO                                                          
****************** End of data ************************************************************


 

 

Physical file used in the program = RESULT2

 



Columns . . . :    1  80                      Browse                         AMIT/QRPGLESRC
SEU==>                                                                              RESULT2
FMT PF .....A..........T.Name++++++RLen++TDpB......Functions+++++++++++++++++++++++++++
*************** Beginning of data *********************************************************
0001.00      A          R RES0002                                                           
0002.00      A            PARTY_NAME    12A                                                 
0003.00      A            PARTY_NUM     12A                                                 
0004.00      A          K PARTY_NUM                                                         
****************** End of data ************************************************************


 

 

 

Logical file used in the program = RESULTL

 


Columns . . . :    1  80                        Edit                        AMIT/QRPGLESRC
SEU==>                                                                             RESULTL
FMT LF .....A..........T.Name++++++.Len++TDpB......Functions+++++++++++++++++++++++++++
*************** Beginning of data *********************************************************
0001.00      A          R RES                       JFILE(RESULT1 RESULT2)                  
0002.00      A          J                           JOIN(RESULT1 RESULT2)                   
0003.00      A                                      JFLD(PARTY_NO PARTY_NUM)                
0004.00      A            PARTY_NO                  JREF(1)                                 
0005.00      A            COUNT                     JREF(1)                                  
0006.00      A            PARTY_NAME                JREF(2)                                 
0007.00      A          K PARTY_NO                                                          
****************** End of data ************************************************************



 

 

Display file used in the program = EMBED_DSP

 

 


Columns . . . :    1  80                      Browse                        AMIT/QRPGLESRC
SEU==>                                                                           EMBED_DSP
FMT DP .....AAN01N02N03T.Name++++++RLen++TDpBLinPosFunctions+++++++++++++++++++++++++++
*************** Beginning of data *********************************************************
0001.00      A                                      DSPSIZ(24 80 *DS3)                      
0002.00      A                                      CA03(03 'EXIT')                         
0003.00      A          R EMBED_SFL                 SFL                                      
0004.00      A            S_PNUM        12A  O  7 13                                        
0005.00      A            S_PNAME       12A  O  7 35                                        
0006.00      A            S_COUNT        2S 0O  7 58                                        
0007.00      A          R EMBED_CTL                 SFLCTL(EMBED_SFL)                       
0008.00      A  42                                  SFLDSP                                   
0009.00      A  41                                  SFLDSPCTL                               
0010.00      A  40                                  SFLCLR                                  
0011.00      A  45                                  SFLEND(*MORE)                           
0012.00      A                                      SFLSIZ(0011)                            
0013.00      A                                      SFLPAG(0010)                             
0014.00      A            FIRSTRRN       4S 0H      SFLRCDNBR(CURSOR)                       
0015.00      A                                  1 28'PARTY ACCOUNT INFORMATION'             
0016.00      A                                  1 73DATE                                    
0017.00      A                                      EDTCDE(Y)                               
0018.00      A                                  2 73TIME                                    
0019.00      A                                  6 13'PARTY NUMBER'                          
0020.00      A                                      DSPATR(HI)                              
0021.00      A                                  6 36'PARTY NAME'                            
0022.00      A                                      DSPATR(HI)                              
0023.00      A                                  6 52'NO. OF ACCOUNTS'                       
0024.00      A                                      DSPATR(HI)                              
0025.00      A          R FOOTER                                                            
0026.00      A                                      OVERLAY                                  
0027.00      A                                 24  3'F3 = EXIT'                             
****************** End of data ************************************************************

 

 

 

 

 

Program demonstrating Embedded SQL using SQLSTMT without cursor

 


Columns . . . :    1  80                      Browse                          AMIT/QRPGLESRC
SEU==>                                                                            EMBED_STM2
FMT H  .....HKeywords++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
*************** Beginning of data **********************************************************
0001.00      HDEBUG(*YES)                                                                    
0002.00      HOPTION(*NODEBUGIO)                                                            
0003.00      FPARTYD    IF   E           K DISK                                             
0004.00      FACCSUBPF  IF   E           K DISK                                             
0005.00      FRESULTL   IF   E           K DISK                                             
0006.00      FEMBED_DSP CF   E             WORKSTN                                          
0007.00      F                                     SFILE(EMBED_SFL:RRN)                     
0008.00      F                                     INFDS(infds)                             
0009.00       *                                                                              
0010.00      Dinfds            DS                                                           
0011.00      DRECNO                  378    379I 0                                          
0012.00       *                                                                             
0013.00      DSQLSTMT1         S            350                                             
0014.00      DSQLSTMT2         S            350                                              
0015.00      DARR              S             10    DIM(9)                                   
0016.00      D                                     PERRCD(3)                                
0017.00      D                                     CTDATA                                   
0018.00       *                                                                             
0019.00      DW_PNUM1          S             12A   INZ(*BLANKS)                              
0020.00      DW_PNUM2          S             12A   INZ(*BLANKS)                             
0021.00      C     *IN03         DOWEQ     *OFF                                             
0022.00      C   03              LEAVE                                                      
0023.00      C                   EXSR      DSPSFL                                           
0024.00      C                   ENDDO                                                       
0025.00      C                   SETON                                            LR        
0026.00      C*                                                                             
0027.00      C*_________________________________________________                            
0028.00      C*_______________CLEAR SUBFILE_____________________                            
0029.00      C*_________________________________________________                            
0030.00      C     CLRSFL        BEGSR                                                      
0031.00      C                   EVAL      RRN=0                                            
0032.00      C                   SETON                                        40            
0033.00      C                   WRITE     EMBED_CTL                                        
0034.00      C                   SETOFF                                       40            
0035.00      C                   ENDSR                                                      
0036.00      C*_________________________________________________                            
0037.00      C*_______________FIRST FUBROUTINE__________________                            
0038.00      C*_________________________________________________                            
0039.00      C*                                                                             
0040.00      C     *INZSR        BEGSR                                                      
0041.00      C                   SETOFF                                       404142        
0042.00      C                   SETOFF                                       45            
0043.00      C                   Z-ADD     *ZEROS        RRN               4 0              
0044.00      C                   EVAL      FIRSTRRN=1                                       
0045.00      C                   EXSR      CLRSFL                                           
0046.00      C                   EXSR      FILSFL                                           
0047.00      C                   EXSR      DSPSFL                                            
0048.00      C                   ENDSR                                                      
0049.00      C*_________________________________________________                            
0050.00      C*_______________FILL SUBFILE__________________                                
0051.00      C*_________________________________________________                            
0052.00      C*                                                                             
0053.00      C     FILSFL        BEGSR                                                      
0054.00      C                   EVAL      SQLSTMT1= 'INSERT INTO RESULT1('+                
0055.00      C                                       'SELECT COUNT(*), '   +                
0056.00      C                                       ARR(7) + ' FROM '     +                
0057.00      C                                       ARR(1) + '/' + ARR(4) +                
0058.00      C                                       ' GROUP BY '+ ARR(7)  +                
0059.00      C                                       ')'                                    
0060.00      C                   EVAL      SQLSTMT2= 'INSERT INTO RESULT2('+                
0061.00      C                                       'SELECT DISTINCT  '    +               
0062.00      C                                       ARR(8) + ', ' + ARR(9) +               
0063.00      C                                       ' FROM '               +               
0064.00      C                                       ARR(1) + '/' + ARR(5)  +               
0065.00      C                                       ')'                                    
0066.00      C                   EVAL      RRN=0                                            
0067.00       *                                                                              
0068.00      C/EXEC SQL                                                                     
0069.00      C+ SET OPTION COMMIT=*NONE                                                     
0070.00      C/END-EXEC                                                                      
0071.00       *                                                                             
0072.00      C/EXEC SQL                                                                      
0073.00      C+ EXECUTE IMMEDIATE :SQLSTMT1                                                 
0074.00      C/END-EXEC                                                                     
0075.00      C/EXEC SQL                                                                      
0076.00      C+ EXECUTE IMMEDIATE :SQLSTMT2                                                 
0077.00      C/END-EXEC                                                                     
0078.00       *                                                                             
0079.00      C                   IF        SQLCOD<>0                                        
0080.00      C                   IF        SQLCOD=100                                        
0081.00      C                   SETON                                        45            
0082.00      C                   ENDIF                                                      
0083.00      C                   SETON                                        LR            
0084.00      C                   RETURN                                                     
0085.00      C                   ENDIF                                                       
0086.00       *                                                                             
0087.00      C                   IF        SQLCOD=0                                         
0088.00      C     *LOVAL        SETLL     RES                                              
0089.00      C                   READ(N)   RES                                              
0090.00      C                   DOW       NOT %EOF(RESULTL)                                 
0091.00      C                   EVAL      RRN=RRN+1                                        
0092.00      C                   EVAL      S_PNUM=PARTY_NO                                  
0093.00      C                   EVAL      S_PNAME=PARTY_NAME                               
0094.00      C                   EVAL      S_COUNT=COUNT                                    
0095.00      C                   WRITE     EMBED_SFL                                        
0096.00      C                   READ      RES                                              
0097.00      C                   ENDDO                                                      
0098.00       *                                                                              
0099.00      C                   IF        %EOF(RESULTL)                                    
0100.00      C                   SETON                                        45            
0101.00      C                   ENDIF                                                      
0102.00       *                                                                             
0103.00      C                   ENDIF                                                       
0104.00       *                                                                             
0105.00      C/EXEC SQL                                                                     
0106.00      C+ DELETE FROM AMIT/RESULT1                                                    
0107.00      C/END-EXEC                                                                     
0108.00       *                                                                              
0109.00      C/EXEC SQL                                                                     
0110.00      C+ DELETE FROM AMIT/RESULT2                                                    
0111.00      C/END-EXEC                                                                      
0112.00      C                   ENDSR                                                      
0113.00      C*_________________________________________________                             
0114.00      C*                                                                             
0115.00      C     DSPSFL        BEGSR                                                      
0116.00      C                   SETON                                        4142          
0117.00      C                   WRITE     EMBED_CTL                                        
0118.00      C                   WRITE     FOOTER                                           
0119.00      C                   READ      EMBED_CTL                                        
0120.00      C                   EVAL      FIRSTRRN=RECNO                                   
0121.00      C                   SETOFF                                       4142          
0122.00      C                   ENDSR                                                      
0123.00 **CTDATA ARR                                                                        
0124.00 AMIT                                                                                
0125.00 ACCSUBPF  PARTYD                                                                    
0126.00 PARTY     PNAME     PNUM                                                             
****************** End of data ***********************************************************


 

 

 

 

 

 

 

OUTPUT


 


                           PARTY ACCOUNT INFORMATION                     2/14/13
                                                                        05:42:09
                                                                               
                                                                               
                                                                                
            PARTY NUMBER        PARTY NAME       NO. OF ACCOUNTS             
            P00000000001          UPI                    07                    
            P00000000002          SHRI                   04                    
            P00000000003          SHMANT                 04                    
            P00000000007          AFTAB                  02                    
                                                                                
                                                                           
                                                                               
                                                                               
                                                                         Bottom
                                                                                
                                                                               
  F3 = EXIT                                                                    


 











User Comments:



Copyright © www.go4as400.com, 2013-2023. Copyright notice   Terms of services   Privacy policy