Embedded SQL Exampleè  Using 2 sequential cursors

 

 

Physical file used in the program: PARTYD

 

       PNAME       PNUM          PADDR       OPNDD  OPNMM  OPNYY   OPNSTS
000001 UPI         P00000000001  CHENNAI        1      1   2,010      2
000002 SHRI        P00000000002  EMPHAL         2      3   2,009      2
000003 SHMANT      P00000000003  CHENNAI        3      5   2,011      2
000004 AFTAB       P00000000007  HOMEIIEA      13      1   2,010      2
000005 AMI         P00000000008  HELLY CORN    12     12   2,012      2
000006 JPY         P00000000009  JEKINSAN      11     11   2,011      2
000007 SHANN       P00000000010  KUPUNUS       24      1   2,012      2
000008 HAPART      P00000000011  KOLINSA       16      1   2,010      2
****** ********  End of report  ********

 

 

 

 

 

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)                       
0007.01      A                                      OVERLAY                                 
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                                 24  3'F3 = EXIT'                             
             ****************** End of data *********************************************************

          

 

PROGAM

 

 
*************** Beginning of data *************************************
HDEBUG(*YES)
HOPTION(*NODEBUGIO)
FEMBED_DSP CF   E             WORKSTN
F                                     SFILE(EMBED_SFL:RRN)
F                                     INFDS(infds)
*
Dinfds            DS
DRECNO                  378    379I 0
*
DARR              S             10    DIM(9)
D                                     PERRCD(3)
D                                     CTDATA
*
DW_PNUM1          S             12A   INZ(*BLANKS)
DW_PNUM2          S             12A   INZ(*BLANKS)
C     *IN03         DOWEQ     *OFF
C   03              LEAVE
C                   EXSR      DSPSFL
C                   ENDDO
C                   SETON                                            LR
C*
C*_________________________________________________
C*_______________CLEAR SUBFILE_____________________
C*_________________________________________________
C     CLRSFL        BEGSR
C                   EVAL      RRN=0
C                   SETON                                        40
C                   WRITE     EMBED_CTL
C                   SETOFF                                       40
C                   ENDSR
C*_________________________________________________
C*_______________FIRST FUBROUTINE__________________
C*_________________________________________________
C*
C     *INZSR        BEGSR
C                   SETOFF                                       404142
C                   SETOFF                                       45
C                   Z-ADD     *ZEROS        RRN               4 0
C                   EVAL      FIRSTRRN=1
C                   EXSR      CLRSFL
C                   EXSR      FILSFL
C                   EXSR      DSPSFL
C                   ENDSR
C*_________________________________________________
C*_______________FILL SUBFILE__________________
C*_________________________________________________
C*
C     FILSFL        BEGSR
C
C                   EVAL      RRN=0
*
C/EXEC SQL
C+ DECLARE C1 CURSOR FOR select count(*), PARTY from AMIT/ACCSUBPF
C+ group by PARTY  order by PARTY
C/END-EXEC
 
C/EXEC SQL
C+ DECLARE C2 CURSOR FOR select PNAME, PNUM from AMIT/PARTYD
C+ order by PNUM
C/END-EXEC
*
*
C/EXEC SQL
C+ OPEN C1
C/END-EXEC
*
C/EXEC SQL
C+ OPEN C2
C/END-EXEC
*
C                   DOW       SQLCOD=0
C/EXEC SQL
C+ FETCH FROM C1 INTO :S_COUNT, :W_PNUM1
C/END-EXEC
C/EXEC SQL
C+ FETCH FROM C2 INTO  :S_PNAME, :W_PNUM2
C/END-EXEC
C                   IF        SQLCOD<0
C                   LEAVE
C                   ENDIF
C                   IF        SQLCOD=100
C                   SETON                                        45
C                   LEAVE
C                   ENDIF
C                   IF        W_PNUM1<>W_PNUM2
C                   ITER
C                   ENDIF
C                   EVAL      RRN=RRN+1
C                   IF        RRN>9999
C                   LEAVE
C                   ENDIF
C                   EVAL      S_PNUM=W_PNUM1
C                   WRITE     EMBED_SFL
C                   ENDDO
*
C/EXEC SQL
C+ CLOSE C1
C/END-EXEC
*
C/EXEC SQL
C+ CLOSE C2
C/END-EXEC
C                   ENDSR
C*_________________________________________________
C*
C     DSPSFL        BEGSR
C                   SETON                                        4142
C                   IF        RRN<=0
C                   SETOFF                                       42
C                   ENDIF
C                   WRITE     FOOTER
C                   EXFMT     EMBED_CTL
C                   EVAL      FIRSTRRN=RECNO
C                   SETOFF                                       4142
C                   ENDSR
****************** 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 © www.go4as400.com, 2013-2023. Copyright notice   Terms of services   Privacy policy