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

 

         

 

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

 

 

 


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


 

OUTPUT

In the program above the query-string generated will be:

select count(*), PARTY from AMIT/ACCSUBPF group by party order by party

select PNAME, PNUM FROM AMIT/PARTYD ORDER BY PNUM

 


                           PARTY ACCOUNT INFORMATION                     2/21/13
                                                                        00:48:00
                                                                               
                                                                                
                                                                               
            PARTY NUMBER           PARTY NAME      NO. OF ACCOUNTS             
            P00000000001          IROBO                  07                    
            P00000000002          KEMON                  04                    
            P00000000003          SHMANT                 04                    
            P00000000007          AFTAB                  04                    
                                                                               
                                                                               
                                                                                
                                                                               
                                                                         Bottom
                                                                                
                                                                               
  F3 = EXIT                                                         

 

 

 

 

 


***NOTE: While making the query string if something you want to put in single quotes 
         then use the example given below:
 
e.g
Querystring for the query statement: SELECT * FTOM PARTYD WHERE PNUM=’P00000000001’ AND 
PNAME LIKE ‘%A%’ will be:
 
 
     C                   EVAL      PNUM001=’P00000000001’
     C                   EVAL      PNAME001=’A    ’
     C                   EVAL      COND_1='PARTY = ' + ''''+PNUM001+''''
     C                   EVAL      COND_2='PNAME LIKE'+'''%' + %TRIM(PNAME001) + '%'''
 
     C                   EVAL      QUERYSTRING = 'SELECT * FTOM PARTYD WHERE' +
     C                                           COND_1 + ' AND ' + COND_1











User Comments:



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