Embedded SQL Example  è Make dynamic query by making a Querystring, PART-II

 

 

Physical file used in the program: PARTYD

 

       PNAME       PNUM          PADDR       OPNDD  OPNMM  OPNYY   OPNSTS
000001 UPI         P00001  CHENNAI        1      1   2,010      2
000002 SHRI        P00002  EMPHAL         2      3   2,009      2
000003 SHMANT      P00003  CHENNAI        3      5   2,011      2
000004 AFTAB       P00007  HOMEIIEA      13      1   2,010      2
000005 AMI         P00008  HELLY CORN    12     12   2,012      2
000006 JPY         P00009  JEKINSAN      11     11   2,011      2
000007 SHANN       P00010  KUPUNUS       24      1   2,012      2
000008 HAPART      P00011  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    A00001    EUR     DFJASHN      NKDAHFJ     P00001
191    A00002    USD     DAJFK        DAJH        P00001
192    A00003    TRY     DAHB         BJDKFA      P00001
190    A00011    EUR     NDJA         CINE        P00002
193    A00004    USD     KIRAN        NDKAL       P00001
194    A00005    KWR     DANK         NKD         P00001
190    A00001    USD     MING         CHINA       P00003
191    A00002    TRY     JHINGA       CHINA       P00003
190    A00021    USD     DFKAF        NKDA        P00002
190    A00011    EUR     NDJA         CINE        P00002
190    A00021    USD     DFKAF        NKDA        P00002
190    A00001    USD     MING         CHINA       P00003
191    A00002    TRY     JHINGA       CHINA       P00003
190    A00001    EUR     DFAKLSJN     NDOAF       P00007
191    A00002    USD     KJDANFK      NKDA        P00007
190    A00001    EUR     DFAKLSJN     NDOAF       P00007
191    A00002    USD     KJDANFK      NKDA        P00007
195    A00006    TRY     APPRINA      SOUTN       P00001
195    A00007    USD     SONATNA      SOUTN       P00001

 

 

 

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                                                            130215
0002.00      A            PARTY_NO      12A                                                  130215
0003.00      A            COUNT          2P 0                                                130215
0004.00      A          K PARTY_NO                                                           130215
        ****************** 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                                                            130215
0002.00      A            PARTY_NAME    12A                                                  130215
0003.00      A            PARTY_NUM     12A                                                  130215
0004.00      A          K PARTY_NUM                                                          130215
****************** 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)                   130215
0002.00      A          J                           JOIN(RESULT1 RESULT2)                    130215
0003.00      A                                      JFLD(PARTY_NO PARTY_NUM)                 130215
0004.00      A            PARTY_NO                  JREF(1)                                  130215
0005.00      A            COUNT                     JREF(1)                                  130215
0006.00      A            PARTY_NAME                JREF(2)                                  130215
0007.00      A          K PARTY_NO                                                           130215
****************** 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)                       130215
0002.00      A                                      CA03(03 'EXIT')                          130215
0003.00      A          R EMBED_SFL                 SFL                                      130215
0004.00      A            S_PNUM        12A  O  7 13                                         130215
0005.00      A            S_PNAME       12A  O  7 35                                         130215
0006.00      A            S_COUNT        2S 0O  7 58                                         130215
0007.00      A          R EMBED_CTL                 SFLCTL(EMBED_SFL)                        130215
0007.01      A                                      OVERLAY                                  130215
0008.00      A  42                                  SFLDSP                                   130215
0009.00      A  41                                  SFLDSPCTL                                130215
0010.00      A  40                                  SFLCLR                                   130215
0011.00      A  45                                  SFLEND(*MORE)                            130215
0012.00      A                                      SFLSIZ(0011)                             130215
0013.00      A                                      SFLPAG(0010)                             130215
0014.00      A            FIRSTRRN       4S 0H      SFLRCDNBR(CURSOR)                        130215
0015.00      A                                  1 28'PARTY ACCOUNT INFORMATION'              130215
0016.00      A                                  1 73DATE                                     130215
0017.00      A                                      EDTCDE(Y)                                130215
0018.00      A                                  2 73TIME                                     130215
0019.00      A                                  6 13'PARTY NUMBER'                           130215
0020.00      A                                      DSPATR(HI)                               130215
0021.00      A                                  6 36'PARTY NAME'                             130215
0022.00      A                                      DSPATR(HI)                               130215
0023.00      A                                  6 52'NO. OF ACCOUNTS'                        130215
0024.00      A                                      DSPATR(HI)                               130215
0025.00      A          R FOOTER                                                             130215
0027.00      A                                 24  3'F3 = EXIT'                              130215
        ****************** End of data ************************************************************

 

 

 

 

 

PROGRAM

 


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



 

 

 

 

 

 

 

OUTPUT


                         

 



                           PARTY ACCOUNT INFORMATION                     2/21/13
                                                                        00:58:14
                                                                               
                                                                               
                                                                               
            PARTY NUMBER           PARTY NAME      NO. OF ACCOUNTS             
            P00001          IROBO                  07                    
            P00002          KEMON                  04                    
            P00003          SHMANT                 04                    
            P00007          AFTAB                  04                   
                                                                               
                                                               
                                                                         Bottom
                                                                                
                                                                               
  F3 = EXIT  











User Comments:



Subscribe

  Email:



Copyright © www.go4as400.com, 2013-2017. Copyright notice