Ü   Embedded SQL using cursor and SQLSTMT

 

 

 

1st Physical file used in the program = PARTYD

 

PNAME       PNUM          PADDR       OPNDD  OPNMM  OPNYY   OPNSTS
UPI         P00000000001  CHENNAI        1      1   2,010      2
SHRI        P00000000002  EMPHAL         2      3   2,009      2
SHMANT      P00000000003  CHENNAI        3      5   2,011      2
AFTAB       P00000000007  HOMEIIEA      13      1   2,010      2
AMI         P00000000008  HELLY CORN    12     12   2,012      2
JPY         P00000000009  JEKINSAN      11     11   2,011      2
SHANN       P00000000010  KUPUNUS       24      1   2,012      2
HAPART      P00000000011  KOLINSA       16      1   2,010      2
 

 

 

 

 

 

2nd Physical file used in the program = ACCSUBPF

 

ORG CODE  ACC NUMBER    CURRENCY  ACCOUNT MNG  COUNTRY     PARTY
190    A00000000001    EUR     DFJASHN      NKDAHFJ     P00000000001
191    A00000000002    USD     DAJFK        DAJH        P00000000001
192    A00000000003    TRY     DAHB         BJDKFA      P00000000001
190    A00000000011    EUR     NDJA         CINE        P00000000002
193    A00000000004    USD     KIRAN        NDKAL       P00000000001
194    A00000000005    KWR     DANK         NKD         P00000000001
190    A00000000001    USD     MING         CHINA       P00000000003
191    A00000000002    TRY     JHINGA       CHINA       P00000000003
190    A00000000021    USD     DFKAF        NKDA        P00000000002
190    A00000000011    EUR     NDJA         CINE        P00000000002
190    A00000000021    USD     DFKAF        NKDA        P00000000002
190    A00000000001    USD     MING         CHINA       P00000000003
191    A00000000002    TRY     JHINGA       CHINA       P00000000003
190    A00000000001    EUR     DFAKLSJN     NDOAF       P00000000007
191    A00000000002    USD     KJDANFK      NKDA        P00000000007
190    A00000000001    EUR     DFAKLSJN     NDOAF       P00000000007
191    A00000000002    USD     KJDANFK      NKDA        P00000000007
195    A00000000006    TRY     APPRINA      SOUTN       P00000000001
195    A00000000007    USD     SONATNA      SOUTN       P00000000001

 

 

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 ********************************************************



 

 


Columns . . . :    1  80            Edit                                          AMIT/QRPGLESRC
SEU==>                                                                                EMBED_STMT
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      FEMBED_DSP CF   E             WORKSTN
0006.00      F                                     SFILE(EMBED_SFL:RRN)
0007.00      F                                     INFDS(infds)
0008.00       *
0009.00      Dinfds            DS
0010.00      DRECNO                  378    379I 0
0011.00       *
0012.00      DSQLSTMT1         S            350
0013.00      DSQLSTMT2         S            350
0014.00      DARR              S             10    DIM(9)
0015.00      D                                     PERRCD(3)
0016.00      D                                     CTDATA
0017.00       *
0018.00      DW_PNUM1          S             12A   INZ(*BLANKS)
0019.00      DW_PNUM2          S             12A   INZ(*BLANKS)
0020.00      C     *IN03         DOWEQ     *OFF
0021.00      C   03              LEAVE
0022.00      C                   EXSR      DSPSFL
0023.00      C                   ENDDO
0024.00      C                   SETON                                            LR
0025.00      C*
0026.00      C*_________________________________________________
0027.00      C*_______________CLEAR SUBFILE_____________________
0028.00      C*_________________________________________________
0029.00      C     CLRSFL        BEGSR
0030.00      C                   EVAL      RRN=0
0031.00      C                   SETON                                        40
0032.00      C                   WRITE     EMBED_CTL
0033.00      C                   SETOFF                                       40
0034.00      C                   ENDSR
0035.00      C*_________________________________________________
0036.00      C*_______________FIRST FUBROUTINE__________________
0037.00      C*_________________________________________________
0038.00      C*
0039.00      C     *INZSR        BEGSR
0040.00      C                   SETOFF                                       404142
0041.00      C                   SETOFF                                       45
0042.00      C                   Z-ADD     *ZEROS        RRN               4 0
0043.00      C                   EVAL      FIRSTRRN=1
0044.00      C                   EXSR      CLRSFL
0045.00      C                   EXSR      FILSFL
0046.00      C                   EXSR      DSPSFL
0047.00      C                   ENDSR
0048.00      C*_________________________________________________
0049.00      C*_______________FILL SUBFILE__________________
0050.00      C*_________________________________________________
0051.00      C*
0052.00      C     FILSFL        BEGSR
0053.00      C                   EVAL      SQLSTMT1= 'SELECT COUNT(*), '   +
0054.00      C                                       ARR(7) + ' FROM '     +
0055.00      C                                       ARR(1) + '/' + ARR(4) +
0056.00      C                                       ' GROUP BY '+ ARR(7)  +
0057.00      C                                       ' ORDER BY ' + ARR(7)
0058.00      C                   EVAL      SQLSTMT2= 'SELECT DISTINCT  '    +
0059.00      C                                       ARR(8) + ', ' + ARR(9) +
0060.00      C                                       ' FROM '               +
0061.00      C                                       ARR(1) + '/' + ARR(5)  +
0062.00      C                                       ' ORDER BY ' + ARR(9)
0063.00      C                   EVAL      RRN=0
0064.00       *
0065.00      C/EXEC SQL
0066.00      C+ PREPARE stmt1 FROM :SQLSTMT1
0067.00      C/END-EXEC
0068.00
0069.00      C/EXEC SQL
0070.00      C+ PREPARE stmt2 FROM :SQLSTMT2
0071.00      C/END-EXEC
0072.00       *
0073.00       ***
0074.00      C/EXEC SQL
0075.00      C+  DECLARE C1 CURSOR FOR stmt1
0076.00      C/END-EXEC
0077.00      C/EXEC SQL
0078.00      C+  DECLARE C2 CURSOR FOR stmt2
0079.00      C/END-EXEC
0080.00       *
0081.00       *
0082.00      C/EXEC SQL
0083.00      C+ OPEN C1
0084.00      C/END-EXEC
0085.00       *
0086.00      C/EXEC SQL
0087.00      C+ OPEN C2
0088.00      C/END-EXEC
0089.00       *
0090.00      C                   DOW       SQLCOD=0
0091.00      C/EXEC SQL
0092.00      C+ FETCH FROM C1 INTO :S_COUNT, :W_PNUM1
0093.00      C/END-EXEC
0094.00      C/EXEC SQL
0095.00      C+ FETCH FROM C2 INTO  :S_PNAME, :W_PNUM2
0096.00      C/END-EXEC
0097.00      C                   IF        SQLCOD<>0
0098.00      C                   IF        SQLCOD=100
0099.00      C                   SETON                                        45
0100.00      C                   ENDIF
0101.00      C                   LEAVE
0102.00      C                   ENDIF
0103.00      C                   IF        W_PNUM1<>W_PNUM2
0104.00      C                   ITER
0105.00      C                   ENDIF
0106.00      C                   EVAL      RRN=RRN+1
0107.00      C                   EVAL      S_PNUM=W_PNUM1
0108.00      C                   WRITE     EMBED_SFL
0109.00      C                   ENDDO
0110.00       *
0111.00      C/EXEC SQL
0112.00      C+ CLOSE C1
0113.00      C/END-EXEC
0114.00       *
0115.00      C/EXEC SQL
0116.00      C+ CLOSE C2
0117.00      C/END-EXEC
0118.00      C                   ENDSR
0119.00      C*_________________________________________________
0120.00      C*
0121.00      C     DSPSFL        BEGSR
0122.00      C                   SETON                                        4142
0123.00      C                   WRITE     EMBED_CTL
0124.00      C                   WRITE     FOOTER
0125.00      C                   READ      EMBED_CTL
0126.00      C                   EVAL      FIRSTRRN=RECNO
0127.00      C                   SETOFF                                       4142
0128.00      C                   ENDSR
0129.00 **CTDATA ARR
0130.00 AMIT
0131.00 ACCSUBPF  PARTYD
0132.00 PARTY     PNAME     PNUM
               ****************** End of data *****************************************


 

 

OUTPUT


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

 

 





User Comments:





Copyright © Go4as400.com, 2013-2017. Copyright notice